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
