Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help V2 Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 8:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:34 AM
Points: 7, Visits: 34
I posted a question a few weeks ago, and due to a external influence i managed to confuse the whole situation. So i thought it best to start a new topic, Hope i haven't broken any rules

I have a table which stores Half Hour Load surveys from energy meters. (Below example only showing one meter)
MeterID                                         	TimeStamp	        WattHours
204107035 1/06/2013 12:00:00 AM 1130.0621337890625
204107035 1/06/2013 12:30:00 AM 1050.9072265625
204107035 1/06/2013 1:00:00 AM 1062.9158935546875
204107035 1/06/2013 1:30:00 AM 1047.809326171875

Now Previously a view was created to Count the TimeStamp, and Sum the Watthours for the month, resulting in the Output below.
MeterID                                         	Count	WattHours
203201582 1440 137497219.28343752
203201588 1440 18056670.550566405
204001454 1440 919623.39525939862
204045881 1440 2139044.9207806392

Now i need to show 3 months data, and link that data to another table using a TimeStamp column. So i need to get a OUTPUT like below so i can link the two ReadTimes columns together in SQL editor(excel)
MeterID                                         	Count	WattHours               Readtime
203201582 1440 137497219.28343752 1/04/2013 12:00:00 AM
203201588 1440 18056670.550566405 1/04/2013 12:00:00 AM
203201582 1440 137497219.28343752 1/05/2013 12:00:00 AM
203201588 1440 18056670.550566405 1/05/2013 12:00:00 AM

This is the other table.
MeterID 	Record	ReadTime 	        WhRec_1 	        WhDel_1
204107004 34 1/04/2013 12:00:00 AM 0.41664002556353807 592056734.45671833
204107026 14 1/04/2013 12:00:00 AM 0 8463153.7957487721
204107001 34 1/04/2013 12:00:00 AM 0.0672000041231513 709314144.91617262
204045882 34 1/04/2013 12:00:00 AM 0.16128000989556313 446459052.31440663


Can anyone help me to create a query to count the timestamps, and sum the watthours and return a extra column with the 1st day of the month, @ 0:00:00 as the readtime so i can link it to another table.
Post #1473425
Posted Monday, July 15, 2013 12:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 2,224, Visits: 2,659
This code will give you the extra column with the first day of the month. I've calculated this with DATEADD combined with the DATEPART of [TimeStamp] and the 0 timevalue (= '01-01-1900'). You probably need to alter the WHERE clause, because I now only select the last three monts from now.

select
MeterId
, count(MeterID) as 'Count'
, sum(WattHours) as 'WattHours'
, dateadd(month, month(timestamp), dateadd(year, year(timestamp)-1900, 0)) as 'MeasuredMonth'
from EnergyMeasure
where
timestamp > dateadd(month, -3, getdate())
group by
MeterID
, dateadd(month, month(timestamp), dateadd(year, year(timestamp)-1900, 0))



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1473472
Posted Monday, July 15, 2013 1:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:34 AM
Points: 7, Visits: 34
HanShi (7/15/2013)
This code will give you the extra column with the first day of the month. I've calculated this with DATEADD combined with the DATEPART of [TimeStamp] and the 0 timevalue (= '01-01-1900'). You probably need to alter the WHERE clause, because I now only select the last three monts from now.

select
MeterId
, count(MeterID) as 'Count'
, sum(WattHours) as 'WattHours'
, dateadd(month, month(timestamp), dateadd(year, year(timestamp)-1900, 0)) as 'MeasuredMonth'
from EnergyMeasure
where
timestamp > dateadd(month, -3, getdate())
group by
MeterID
, dateadd(month, month(timestamp), dateadd(year, year(timestamp)-1900, 0))



Thank you so much :D
Post #1473495
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse