Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-28
10
Medium Priority
?
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 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