A totalizing question - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

A totalizing question

Author

Topic

marcoviy
Starting Member

6 Posts

Posted - 2011-08-19 : 20:02:47

Hey folks,
Could you please help me?

I need a query to calculate the cumulative total reading, but for those meter_ids that are not in a certain date should also be shown within the calculated value in previous date. In this example, For APR 22 there isn't any value for METER_ID=2, but it is shown as a result of 12 (because 12+0=12). In this case, the query should get as shown at the end of this thread.

Declare

@myTable Table (ReadingDate Date, id int, Reading TinyInt)

Insert
Into @myTable
Select
'04/21/2011', 1, 10 Union all
Select
'04/21/2011', 2, 12 Union All
Select
'04/22/2011', 1, 15 Union All
Select
'04/22/2011', 1, 14 Union All
Select
'04/23/2011', 2, 20 Union All
Select
'04/23/2011', 2, 25 Union All
Select
'04/24/2011', 1, 20 Union All
Select
'04/24/2011', 2, 25

I used the following query:

Select MainQry.Date, MainQry.id
,SUM(Distinct MainQry.Reading) + IsNull(SUM(Distinct SubQry.Reading), 0) Reading From
@myTable
As MainQry
Left Outer Join @myTable As SubQry On MainQry.Date > SubQry.Date AND MainQry.id=SubQry.id
Group By MainQry.Date, MainQry.id

The problem with this query is that I cannot get the accumulative reading for meters which do not have values on a certain date, e.g. METER_ID=2 on date 2011-04-22; or for METER_ID=1 on date 2011-04-23

DATE METER_ID READING
2011-04-21 1 10
2011-04-21 2 12
2011-04-22 1 39 <<-
2011-04-23 2 57 <<-
2011-04-24 1 59
2011-04-24 2 82

The result of the query should be the following:

DATE METER_ID READING
2011-04-21 1 10
2011-04-21 2 12
2011-04-22 1 10+15=25
2011-04-22 2 12+0=12 <<- this row is missing
2011-04-23 1 25+0=25 <<- this row is missing
2011-04-23 2 12+20+25=57
2011-04-24 1 25+20=45
2011-04-24 2 57+25=82

Thank you very much,

Marco

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-20 : 00:11:09

[code]
; with
dates as
(
select ReadingDate = dateadd(day, number, '2011-04-21')
from master..spt_values v
where v.type = 'P'
and v.number <= datediff(day, '2011-04-21', '2011-04-24')
),
meters as
(
select distinct id
from @myTable
),
readings as
(
select ReadingDate, id, Reading = sum(Reading)
from @myTable
group by ReadingDate, id
)
select d.ReadingDate, m.id, t.Reading, c.cummReading
from dates d
cross join meters m
left join readings t on d.ReadingDate = t.ReadingDate
and m.id = t.id
outer apply
(
select cummReading = sum(x.Reading)
from readings x
where x.ReadingDate <= d.ReadingDate
and x.id = m.id
) c
order by d.ReadingDate, m.id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources