SQL Tips by Namwar Rizvi

November 10, 2008

Generate rows for missing dates

Filed under: SQL Server 2005,tips,TSQL — namwar @ 9:16 PM
Tags: ,

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'
Advertisements

Blog at WordPress.com.