Generate rows for missing dates

For all those reports or queries where you want to list a row for each day such that if no data is available for that day then you need zero against that date then you can use the Calendar table for the missing dates.

For generating a calendar table efficiently, you can refer to my earlier post here

Assuming you have a calendar table, use the following query to generate the required result

Use tempdb
Go

/*Create a sample table*/
drop table sampleData
go
Create table sampleData
(
    id int IDENTITY(1,1) NOT NULL,
    transactionDate datetime,
    Qty int

)

Insert into sampleData (transactionDate,Qty) values ('20081101',2)
Insert into sampleData (transactionDate,Qty) values ('20081101',3)

Insert into sampleData (transactionDate,Qty) values ('20081104',1)
Insert into sampleData (transactionDate,Qty) values ('20081104',2)
Go

Select C.CalendarDate,Isnull(A.TotalQty,0) as TotalQty
from
Calendar C
left outer join
(
    Select transactionDate,Sum(Qty) as TotalQty
    from sampleData
    group by transactionDate
) A
on C.CalendarDate=A.transactionDate
Where C.CalendarDate between '20081101' AND '20081130'