SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help V2


Query Help V2

Author
Message
akamorse
akamorse
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 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 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.
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9064 Visits: 3718
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
akamorse
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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 BigGrin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search