I have database MS2008 that has a table called HISTORY2012. These contain thousands of rows of historical data. Most important columns are DATASETNAME which is varchar(20), VALUE which is varchar(12) that I have to convert to FLOAT, and TIME that is a datetime (yyyy-mm-dd hh:mm:ss). I need to take the VALUES of rows based on TIME of hourly even though there is data that has times that are every 15 seconds for instance every hour I need to take DATASETNAME that is = 'GCECLD' and DATASETNAME that = 'EPCOLD' each has values and subtract them for the new column called GCERCOT But I only want on the hour. I also need to display the date. So I know this would be a GROUP BY effort. But I am stuck
DATASETNAME VALUE TIME
GCECLD 125.23 2012-01-01 00:00:00
GCECLD 125.65 2012-01-01 00:00:15
EPCOLD 19.12 2012-01-01 00:00:00
GCECLD 125.65 2012-01-01 00:00:30
GCECLD 125.65 2012-01-01 00:00:45
GCECLD 125.65 2012-01-01 00:01:00
GCECLD 129.65 2012-01-01 01:00:00
EPCOLD 18.12 2012-01-01 01:00:00
All the way through the end of the year.
So the output would be:
DATE TIME GCERCOT
01-01-2012 00:00:00 106.11
01-01-2012 01:00:00 111.53
I hope I have given as much detail as I can. I know some basics in Selecting and queries but Math functions and grouping still escape me. Thanks.