Query - Count By Day from Table with Effective Date Field

[EnergyTed]

Hi

I am trying to show a count by day from a table with an Effective Date field.

The table has different rows for each different state the item is in.

My query is something like this -

Select COUNT(Item), YEAR(EffectiveFrom), MONTH(EffectiveFrom), DAY(EffectiveFrom)

From table

Where State = IN(1,4,5)

Group By YEAR, MONTH, DAY, Count.

Current result is something like this -

Owner, Year, Month, Day, Count
A, 2010, 01, 01, 5
B, 2010, 01, 01, 6
C, 2010, 01, 01, 8
A, 2010, 01, 02, 4
B, 2010, 01, 02, 7
C, 2010, 01, 02, 7

This produces individual counts per day, but does not provide a cumulative total per day.

I am trying to achieve something like this, bearing in mind the item can change its state, thus the cumulative total does not always increase -

Owner, Year, Month, Day, Count
A, 2010, 01, 01, 5
B, 2010, 01, 01, 6
C, 2010, 01, 01, 8
A, 2010, 01, 02, 9
B, 2010, 01, 02, 13
C, 2010, 01, 02, 15

I have read some suggestions regarding the use of a temporary calendar table. Can you provide some info/advice that I should go down this route?

Any help would be greatly appreciated.

Cheers

Ted

 

[markros]

Take a look at ideas from
MSDN thread with many helpful links[/a]

[url=http://www.pluralsight.com/main/olt/?ac=1905603345]PluralSight Learning Library

 

[RyanEK]

How about..

declare @temp table (owner char, year int, month int, day int, count int)
insert into @temp values ('A',2010,1,1,5)
insert into @temp values ('B',2010,1,1,6)
insert into @temp values ('C',2010,1,1,8)
insert into @temp values ('A',2010,1,2,4)
insert into @temp values ('B',2010,1,2,7)
insert into @temp values ('C',2010,1,2,7)
insert into @temp values ('A',2010,1,3,9)

;with mycte as (
select *, 
 CAST(CAST(year AS VARCHAR(4))+
 RIGHT('0'+CAST(month AS VARCHAR(2)),2)+
 RIGHT('0'+CAST(day AS VARCHAR(2)), 2) AS DATETIME) as tdate
from @temp)

select owner, year, month, day,
isnull((select SUM(count) 
 from mycte t1 where t1.owner = t.owner and t1.tdate <= t.tdate),count) as count
from mycte t
order by tdate

Ryan