Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wally.randall@comcast.net
Starting Member
2 Posts |
 Posted - 2008-08-26 : 16:36:47
|
| This query has the purpose of producing a result set used to plot a graph showing cummulative electric usage over the timespan of a month. The query runs but does "accumulate". Each value is just the sum of that day. Why is the "day_value" not showing the accumulated total of the subquery "total_value" amounts?select X.building, X.day ,SUM(total_value) day_value from (SELECT a.building building, (datepart(dd, a.total_start_datetime)) day, SUM(b.total_value) total_value FROM Meter_Total_Rpt a cross JOIN Meter_Total_Rpt b WHERE a.meter_total_rpt_ID >= b.meter_total_rpt_ID AND (DATEPART(yyyy, a.total_start_datetime) = DATEPART(yyyy, b.total_start_datetime) AND DATEPART(mm, a.total_start_datetime) = DATEPART(mm, b.total_start_datetime) AND DATEPART(dd, a.total_start_datetime) = DATEPART(dd, b.total_start_datetime)) and a.building = b.building and a.meter_id = b.meter_id and a.util_svc_ID = 4 AND a.util_svc_id = b.util_svc_ID and a.mtr_total_timeperiod_type_ID = 2 and a.mtr_total_timeperiod_type_ID = b.mtr_total_timeperiod_type_ID and (DATEPART(yyyy, a.total_start_datetime)=year(getdate()) )and (DATEPART(mm, a.total_start_datetime)=month(getdate()) ) GROUP BY a.building,(datepart(dd, a.total_start_datetime)) ) X group by building,day |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-26 : 16:41:22
|
| Could you show us a data example? Show us what the derived table (it's not a subquery) is returning and then what the outer query should return. You've probably got an issue with your GROUP BY though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
|
|
Cumulative Query does not accumulate