Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Location
    Croatia
    Posts
    5

    Unanswered: Read every nth row

    Hello, hope that I've opened this topic is in the right forum.

    I have a system which records (in bin values) temperature every 10 seconds in a SQL database. In one project I need those temperature values for last two years.

    I have the script in VB that exports to Excell all temperature values. But in that way I have a lot of problems because I can't export more then 65000 values to Excel. Is there a way to export only every 60th row? I would like to have values recorded every hour (not every 10 seconds). Is there something like .movenext + 60?
    Last edited by dstuhne; 02-10-10 at 02:11.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this is SQL 2005+ then you can use ROW_NUMBER and modulus to get every 60th row.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Location
    Croatia
    Posts
    5
    Quote Originally Posted by pootle flump View Post
    If this is SQL 2005+ then you can use ROW_NUMBER and modulus to get every 60th row.
    And If not?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so there is a row for every 10 seconds and you want to remove all the rows that are not on the hour exactly?

    Is your data reliable? Instead of going for every 60th row (which is a pest in SQL 2000-) we could just filter on that criterion.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2010
    Location
    Croatia
    Posts
    5
    Quote Originally Posted by pootle flump View Post
    Ok - so there is a row for every 10 seconds and you want to remove all the rows that are not on the hour exactly?

    Is your data reliable? Instead of going for every 60th row (which is a pest in SQL 2000-) we could just filter on that criterion.
    Yes, there is a row for every 10 seconds.

    If I export every row I will get to much data (6.307.200 rows for two years) and we can't export more then 65000 to Excel. If I take only the rows that are on the hour exactly I will get only 17520 rows.

    Yes, I think that my data is reliable. It should be.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    --http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx
    USE test
    GO
    
    SELECT TOP 100 *
    FROM    dbo.numbers
    INNER JOIN
            dbo.your_data
    ON  your_data.date_column = DATEADD(hh, numbers.number, '19960101T10:00:00'/*You should put the first date of your data here*/)
    WHERE   numbers.number BETWEEN 0 AND 17520 --best to increase or calculate this upper limit
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2010
    Location
    Croatia
    Posts
    5
    That code gave me an idea what to do. Not really sure if it will work but I can try.

    Interesting thing is that if I copy/paste 60x "oRs.movenext" in the loops of my code I get exactley what I want. But it doesn't work all the time. For example, it works from 1.12.2009. to 15.12.2009. But it doesn't work from 1.12.2009 to 31.12.2009.... Or any longer time interval.

    Thanks pootle flump!

  8. #8
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    Is there something like .movenext + 60?
    Yes. Use the Move method.

    Code:
    Do While Not oRs.EOF
        n = n + 1
        R = n + 1
        .Worksheets("sheet1").Cells(R, C).Value = oRs.Fields(C).Value
    '    oRs.MoveNext
        oRs.Move 60
    Loop

  9. #9
    Join Date
    Feb 2010
    Location
    Croatia
    Posts
    5
    Quote Originally Posted by brucevde View Post
    Yes. Use the Move method.

    Code:
    Do While Not oRs.EOF
        n = n + 1
        R = n + 1
        .Worksheets("sheet1").Cells(R, C).Value = oRs.Fields(C).Value
    '    oRs.MoveNext
        oRs.Move 60
    Loop
    Yes, that's what I need! Thanks brucevde!
    Last edited by dstuhne; 02-10-10 at 02:11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •