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?
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.
SELECT TOP 100 *
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
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.