query for totals over a rage of dates 2

[scotttom]

I'm looking for some guidance for getting a sum of one column when certain dates match..

Basically I'm trying to map capacity usage.

I have a table like this that holds the data...

Number DateStart DateComplete
200 1/1/2011 1/3/2011
300 1/2/2011 1/4/2011


What I'm looking for is results like this...

1/1/2011 200
1/2/2011 500
1/3/2011 500
1/4/2011 200

I so frequently have gotten great techniques from this forum I thought I'd start here.

Thanks in advance.

 

[PWise]

what you will need is a calander table

1/1/1
1/2/1
....
12/31/12

starting as erley as yo need it as far ahead as you need

Select dayofyear ,sum(number)
From calander
inner join capacity
on dayofyear between DateStart And DateComplete
Group by dayofyear

 

[scotttom]

Perfect. Exactly what I needed.

THanks.

 

[simian336]

great link about calendar tables.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi