asked on

MS SQL Calculate 2 values in same column different that groups by datetime (hourly)

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.

SThaya

you can use pivot in sql query

http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

rayburnelectric

ASKER

I have other DATASETNAMES like TVECLC,FECLD etc. that have values in same date hour range. This doesn't fix issue with grouping by TIME. I will be calling the view from a Web Page but need to test it in SQL management Studio first.

Guy Hengel [angelIII / a3]

You want to GROUP BY HOUR(time), CONVERT(VARCHAR(10), time, 120)

rayburnelectric

ASKER

ok But I need the full SELECT STATEMENT on getting the columns with those where clauses to subtract the value.

Ioannis Paraskevopoulos

Hi,

Try this

SELECT	CONVERT(VARCHAR(10),[TIME],101) AS DateOnly,
		CONVERT(VARCHAR(8),CONVERT(DATETIME,DATEADD(hh,DATEPART(hh, [TIME]),CONVERT(VARCHAR(10),[TIME],101))),108),
		SUM(CASE WHEN DATASETNAME = 'GCECLD' THEN CONVERT(FLOAT,[VALUE]) END) AS 'GCECLD',
		SUM(CASE WHEN DATASETNAME = 'EPCOLD' THEN CONVERT(FLOAT,[VALUE]) END) AS 'EPCOLD',
		SUM(CASE WHEN DATASETNAME = 'GCECLD' THEN CONVERT(FLOAT,[VALUE]) END) -
		SUM(CASE WHEN DATASETNAME = 'EPCOLD' THEN CONVERT(FLOAT,[VALUE]) END) AS 'GCERCOT '
FROM	HISTORY2012
GROUP BY CONVERT(VARCHAR(10),[TIME],101),
		DATEPART(hh, [TIME])

Open in new window

Giannis

rayburnelectric

ASKER

This was Close. The query takes a bit. But it does not ORDER BY DATE THEN TIME. Also I need to be able to pass a variable to it I may several scenarios where I am subtracting one DATASETNAME from another based on hourly values.

ASKER CERTIFIED SOLUTION

Ioannis Paraskevopoulos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rayburnelectric

ASKER

That worked for most part. I will be building the query into a view or from Razor. Just takes about 17 seconds. Also ORDER BY 1,2 ASC orders it by first and seconds column. Thank you.

Ioannis Paraskevopoulos

Hi,

Thanks. Keep in mind that although ORDER BY 1,2 could be a quick way to do it, but you should consider what if you ever put an extra column before them? the order will break. If you explicitly state how you order the results, you won't have to worry for that case.

ORDER BY 1,2 is fine though for most cases.

Giannis

rayburnelectric

ASKER

Very true. This query will populate a chart in essence I would only need DATE TIME and The difference value.