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

/*Create a sample table*/
drop table sampleData
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)

Select C.CalendarDate,Isnull(A.TotalQty,0) as TotalQty
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'

1 Comment »

  1. This example was extremely helpful. Thanks!

    Comment by Andy Bauch — June 18, 2009 @ 1:21 AM | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: