Background
In many reports, one of the axis will be the a date range, either monthly or daily. However, there may be no data in a specific month or on a specific day. There will be no record when joining different tables. For example, if no user on 2008-05-03, the row of 2008-05-03 will not appear
Date | Number of User |
2008-05-01 | 3 |
2008-05-02 | 5 |
2008-05-04 | 6 |
This SQL script generates a date range for left/right joining other tables so the result will include date with no data.
Date | Number of User |
2008-05-01 | 3 |
2008-05-02
| 5
|
2008-05-03 | 0 |
2008-05-04
| 6
|
Create Function dbo.fnDateTable
(
@StartDate datetime,
@EndDate datetime,
@DayPart char(5) -- support 'day','month','year','hour', default 'day'
)
Returns @Result Table
(
[Date] datetime
)
As
Begin
Declare @CurrentDate datetime
Set @CurrentDate=@StartDate
While @CurrentDate<=@EndDate
Begin
Insert Into @Result Values (@CurrentDate)
Select @CurrentDate=
Case
When @DayPart='year' Then DateAdd(yy,1,@CurrentDate)
When @DayPart='month' Then DateAdd(mm,1,@CurrentDate)
When @DayPart='hour' Then DateAdd(hh,1,@CurrentDate)
Else
DateAdd(dd,1,@CurrentDate)
End
End
Return
End
Points of Interest
Parameterized User-defined function is very useful. And caller to the function can use the result directly just like a table.