[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2013-01-28
10
Medium Priority
?
397 Views
Last Modified: 2013-01-28
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.
0
Comment
Question by:rayburnelectric
10 Comments
 
LVL 11

Expert Comment

by:SThaya
ID: 38826865
0
 

Author Comment

by:rayburnelectric
ID: 38826889
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38826912
You want to GROUP BY HOUR(time), CONVERT(VARCHAR(10), time, 120)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:rayburnelectric
ID: 38826923
ok But I need the full SELECT STATEMENT on getting the columns with those where clauses to subtract the value.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38826929
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
0
 

Author Comment

by:rayburnelectric
ID: 38826960
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.
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 1280 total points
ID: 38827014
Hi,

Does this cover you?

DECLARE @Minuend AS VARCHAR(20)
DECLARE @Subtrahend AS VARCHAR(20)
SET @minuend 	= 'GCECLD'
SET @subtrahend = 'EPCOLD'
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 = @minuend THEN CONVERT(FLOAT,[VALUE]) END) AS Minuend ,
		SUM(CASE WHEN DATASETNAME = @subtrahend THEN CONVERT(FLOAT,[VALUE]) END) AS Subtrahend,
		SUM(CASE WHEN DATASETNAME = @minuend THEN CONVERT(FLOAT,[VALUE]) END) -
		SUM(CASE WHEN DATASETNAME = @subtrahend THEN CONVERT(FLOAT,[VALUE]) END) AS 'difference'
FROM	zz_test1
GROUP BY CONVERT(VARCHAR(10),[TIME],101),
		DATEPART(hh, [TIME])
ORDER BY  CONVERT(VARCHAR(10),[TIME],101),
		DATEPART(hh, [TIME])

Open in new window



Giannis
0
 

Author Comment

by:rayburnelectric
ID: 38827055
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.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38827102
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
0
 

Author Comment

by:rayburnelectric
ID: 38827116
Very true. This query will populate a chart in essence I would only need DATE TIME and The difference value.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question