Solved

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

Posted on 2013-01-28
10
391 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

724 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