Query Help V2

Question

Query Help V2

akamorse

SSC Rookie

Points: 25
More actions
July 14, 2013 at 8:55 pm

#275267

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 :unsure:
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 AM1130.0621337890625
204107035 1/06/2013 12:30:00 AM1050.9072265625
204107035 1/06/2013 1:00:00 AM1062.9158935546875
204107035 1/06/2013 1:30:00 AM1047.809326171875
Now Previously a view was created to Count the TimeStamp, and Sum the Watthours for the month, resulting in the Output below.
MeterID CountWattHours
203201582 1440137497219.28343752
203201588 144018056670.550566405
204001454 1440919623.39525939862
204045881 14402139044.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 CountWattHours Readtime
203201582 1440137497219.28343752 1/04/2013 12:00:00 AM
203201588 144018056670.550566405 1/04/2013 12:00:00 AM
203201582 1440137497219.28343752 1/05/2013 12:00:00 AM
203201588 144018056670.550566405 1/05/2013 12:00:00 AM
This is the other table.
MeterID RecordReadTime WhRec_1 WhDel_1
204107004 341/04/2013 12:00:00 AM0.41664002556353807592056734.45671833
204107026 141/04/2013 12:00:00 AM0 8463153.7957487721
204107001 341/04/2013 12:00:00 AM0.0672000041231513709314144.91617262
204045882 341/04/2013 12:00:00 AM0.16128000989556313446459052.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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

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’! **

akamorse SSC Rookie Points: 25 More actions · Answer 2

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 😀