?
Solved

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

Posted on 2013-01-28
10
Medium Priority
?
392 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

801 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