SQL Server – Generate Calendar using TSQL

,

Introduction

Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day.

Showing the events in the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises and I got a chance to use TSQL to generate the Calendar.

Implementation

Below is the TSQL which I came up with to generate the Calendar -

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.

DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.

 

--Find and set the Start & End Date of the said Month-Year

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'

DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

 

;WITH Dates AS (

  SELECT 

    @StartDate Dt

  UNION ALL

  SELECT 

    DATEADD(DAY,1,Dt) 

  FROM 

    Dates 

  WHERE 

    DATEADD(DAY,1,Dt) <= @EndDate

),Details AS (

  SELECT 

    DAY(Dt) CDay,

    DATEPART(WK,Dt) CWeek,

    MONTH(Dt) CMonth,

    YEAR(Dt) CYear,

    DATENAME(WEEKDAY,Dt) DOW,

    Dt 

  FROM 

    Dates

)

--Selecting the Final Calendar

SELECT

  Sunday,

  Monday,

  Tuesday,

  Wednesday,

  Thursday,

  Friday,

  Saturday

FROM

  (SELECT CWeek,DOW,CDay FROM Details) D

PIVOT

(

  MIN(CDay)

  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

) AS PVT

ORDER BY

  CWeek

Output:

Calendar

Hope, this will help!

 

Edit : 14th April 2013 – Replacing NULLS with previous & next dates.

Below is the modified script that will display the dates from the previous & next month instead of NULL

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.

DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.

 

--Find and set the Start & End Date of the said Month-Year

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'

DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

 

--Set the no. of days we want from the Previous & the Next month.

DECLARE @DaysOfPreviousAndNextMonth AS INT = 6

 

;WITH Dates AS (

  SELECT 

    (@StartDate - @DaysOfPreviousAndNextMonth) Dt

  UNION ALL

  SELECT 

    DATEADD(DAY,1,Dt) 

  FROM 

    Dates 

  WHERE 

    DATEADD(DAY,1,Dt) <= (@EndDate + @DaysOfPreviousAndNextMonth)

),Details AS (

  SELECT 

    DAY(Dt) CDay,

    DATEPART(WK,Dt) CWeek,

    MONTH(Dt) CMonth,

    YEAR(Dt) CYear,

    DATENAME(WEEKDAY,Dt) DOW,

    Dt 

  FROM 

    Dates

),FirstSunday AS (

  SELECT

    MIN(Dt) AS FirstSunday

  FROM

    Details

  WHERE

    DOW = 'Sunday'    

),LastSaturday AS (

  SELECT

    MAX(Dt) AS LastSaturday

  FROM

    Details

  WHERE

    DOW = 'Saturday'

),FilteredDetails AS (

  SELECT 

    CWeek,

    DOW,

    CDay,

    Dt,

    ROW_NUMBER() OVER (PARTITION BY DOW ORDER BY Dt) Rno 

  FROM 

    Details

  WHERE

    Dt BETWEEN (SELECT TOP 1 FirstSunday FROM FirstSunday) 

      AND (SELECT TOP 1 LastSaturday FROM LastSaturday)

)

--Selecting the Final Calendar

SELECT

  Sunday,

  Monday,

  Tuesday,

  Wednesday,

  Thursday,

  Friday,

  Saturday

FROM

  (SELECT 

    Rno,DOW,CDay 

   FROM 

    FilteredDetails) D

PIVOT

(

  MIN(CDay)

  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

) AS PVT

ORDER BY

  Rno

Rate

Share

Share

Rate