SQL Tips by Namwar Rizvi

May 26, 2007

Generate Results for missing rows

Filed under: Query,tips,TSQL,Tuning — namwar @ 11:36 PM

Sometimes we require to display values for all days of a month. The problem with this is if you have properly normalized database then there is a chance that you may be recording data for only those days which have any value or in simple words you are recording the data as date, value pair. If for example, there is no data for 17th day of the month then we will not record an empty row for it. It is a proper and correct approach but If we need a report to display the data for all days and we require 0 as a value for all missing days then we can use the following TSQL approach to generate the data for all days including missing days also:

–Disable SQL Server intermediate messages
Set NoCount On

–Create test Table containing daily data
Declare @m_TestTable table (DayId int, value int)

–Insert values for some days
Insert into @m_TestTable values(1,30)
Insert into @m_TestTable values(4,20)
Insert into @m_TestTable values(9,33)
Insert into @m_TestTable values(12,77)
Insert into @m_TestTable values(15,177)

—Create Numbers table and fill it with 1-30
Declare @m_Days table (id int)
Declare @m_index int
Select @m_index=1
While @m_index<31
Begin
Insert into @m_Days values(@m_index)
Select @m_index=@m_index+1

End

—Run the following query to return value of each day in a month.
Select B.Id,Isnull(A.Value,0) From @m_TestTable A Right Outer Join @m_Days B On A.DayId=B.id

Advertisements

Leave a Comment »

No comments yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: