Generating Calendar Table dynamically and efficiently

A while back I had posted a way to generate million rows in one second. The main purpose was to generate a number table which can be utilized in many scenarios and it is so useful that I will recommend having this table in every database where we need to perform temporal calculations. One of these scenarios is to generate timesheet data where we need a row for each day regardless the user has recorded a time against it or not. This is due to generate a full calendar of payment period and to calculate the payments for each date.

Following is the TSQL query which generates the rows for each day for the required number of years, interesting point is that given logic automatically cater for Leap year and does not produce incorrect dates. I hope you will find this SQL code useful. Please note that the logic used to generate number table is proposed in SQL Server 2005 TSQL Querying by Microsoft press.

–Following variables can be used

–as stored procedure parameters

Declare @p_NumberOfRows int

Declare @p_StartDate DateTime

Declare @p_NumberOfYears int

Select @p_StartDate=‘20071231′

Select @p_NumberOfYears=1

–Calculate no. of days in a year. It can be 365 or 366 depending on leap year

Select @p_NumberOfRows=Datediff(day,@p_StartDate,Dateadd(Year,@p_NumberOfYears,@p_StartDate));

 

With Base As

(

      Select 1 as n

      Union All

      Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))

),

Expand As

(

      Select 1 as C

      From Base as B1, Base as B2

),

Nums As

(

      Select Row_Number() OVER(ORDER BY C) As n

      From Expand

)

 

—Main Query to generate Calendar table

Select

      n as DayNumber,

      Convert(varchar, Dateadd(day,n,@p_StartDate),113) as CalendarDate

From Nums

Where n<=@p_NumberOfRows

 

–Remove Maximum Recursion level constraint

OPTION (MaxRecursion 0);