在 SQL Server 中生成日期表

背景

在许多报表中，一个轴通常会是日期范围，可以是按月或按日。但是，在特定的月份或日期可能没有数据。在连接不同的表时，不会有记录。例如，如果在 2008-05-03 没有用户，那么 2008-05-03 的行将不会出现。

日期	用户数量
2008-05-01	3
2008-05-02	5
2008-05-04	6

这个 SQL 脚本生成一个日期范围，用于与其他表进行左/右连接，以便结果包含没有数据的日期。

日期	用户数量
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
        

关注点

参数化的用户自定义函数非常有用。调用者可以直接像使用表一样使用函数的结果。