July 14, 2013 at 8:55 pm
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.
July 15, 2013 at 12:43 am
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))
July 15, 2013 at 1:16 am
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 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply