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
Solved

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

Posted on 2013-01-28
10
389 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 320 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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