Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by sue.scheepers on 16 April 2013

How do you fill the nulls with next or previous?

Posted by Vedran Kesegic on 16 April 2013

Nice!

Posted by Bidyut-437210 on 16 April 2013

Thanks for sharing the code.

Another alternative solution.

BEGIN

/******************************************************************************

Author      : Bidyut Bhattacharjee

Date        : 16-Apr-2013

Description : Creates a calendar for a specific month

******************************************************************************/

SET NOCOUNT ON

/******************************************************************************

-- Declare Variables

*******************************************************************************/  

DECLARE @mmonth NVARCHAR(5),

@myear NVARCHAR(10),

@lastday INT,

@firstday INT,

@tsql NVARCHAR(100),

@dateval NVARCHAR(15),

@dayval NVARCHAR(15),

@row INT = 1,

@daycount INT = 0

/******************************************************************************

-- Create temporary table Variables -- Drop table if it exists

*******************************************************************************/

CREATE TABLE tab (

Row INT,

Sunday NVARCHAR(6),

Monday NVARCHAR(6),

Tuesday NVARCHAR(6),

Wednesday NVARCHAR(6),

Thursday NVARCHAR(6),

Friday NVARCHAR(6),

Saturday NVARCHAR(6)

)

/******************************************************************************

-- Insert row values for all the days

*******************************************************************************/

INSERT INTO tab (Row)

SELECT '1'

INSERT INTO tab (Row)

SELECT '2'

INSERT INTO tab (Row)

SELECT '3'

INSERT INTO tab (Row)

SELECT '4'

INSERT INTO tab (Row)

SELECT '5'

/******************************************************************************

-- Set variables

*******************************************************************************/

SET @firstday = 1

SET @mmonth = MONTH(getdate()) + 8

SET @myear = YEAR(getdate())

   SELECT @lastday = DAY(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, (@myear + '-' + @mmonth + '-01')) + 1, 0)))

   SET @daycount = 1

/******************************************************************************

-- While loop starts - Populate the calendar

*******************************************************************************/

WHILE (@firstday <= @lastday)

BEGIN

SET @dateval = @myear + '-' + @mmonth + '-' + CONVERT(NVARCHAR(5), @firstday)

SET @dayval = DATENAME(WEEKDAY, @dateval);

IF (

SELECT LEN(SATURDAY)

FROM tab

WHERE ROW = @row

) IS NULL

BEGIN

   -- Set the dynamic SQL

SET @tsql = 'UPDATE tab SET ' + @dayval + ' = (DAY(''' + @dateval + ''')) WHERE ROW= ' + '''' + convert(NVARCHAR(6), @row) + ''''

SET @firstday = (@firstday + 1)

           EXEC SP_EXECUTESQL @tsql

END

IF (@daycount = 7)

BEGIN

SET @daycount = 1

SET @row = @row + 1

END

ELSE

SET @daycount = @daycount + 1

END

/******************************************************************************

-- Select the data from the calendar

*******************************************************************************/

SELECT Sunday,

Monday,

Tuesday,

Wednesday,

Thursday,

Friday,

Saturday

FROM tab

drop table tab

END

Posted by Bidyut-437210 on 16 April 2013

Just set the month variable ( @mmonth = MONTH(getdate()) ) to generate the calendar

Posted by vinaypugalia on 16 April 2013

@sue.scheepers, below is the modified code that will show the previous & Next months dates 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

Posted by pricardo03 on 16 April 2013

Nice code, I test both codes and the original got de best performace and clear Execution Plan and best execution time. need only 0.265 seconds

the second one is using a physical table incresing I/O, a while (this is more slow then CTE) and use an update and drop the table. this create lot differents plans and need 2.984 seconds

thanks to all, these code is a very good example for to teach in my class.

Posted by Dave Vroman on 16 April 2013

An alternate would be to set the nulls to spaces. A small modification to the original code would be:

SELECT

 ISNULL(CAST(Sunday AS VARCHAR(2)), '') Sun,

 ISNULL(CAST(Monday AS VARCHAR(2)), '') Mon,

 ISNULL(CAST(Tuesday AS VARCHAR(2)), '') Tue,

 ISNULL(CAST(Wednesday AS VARCHAR(2)), '') Wed,

 ISNULL(CAST(Thursday AS VARCHAR(2)), '') Thu,

 ISNULL(CAST(Friday AS VARCHAR(2)), '') Fri,

 ISNULL(CAST(Saturday AS VARCHAR(2)), '') Sat

Posted by gerhard.mueller-1049353 on 17 April 2013

nice code, but the output of DATENAME(WEEKDAY,0) differs for diff. coutries, so the following line may be used to find the correction of the above 2 lines with a list of weekdays in english: Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday

SELECT     DATENAME(WEEKDAY,0) + ',' + DATENAME(WEEKDAY,1) + ',' + DATENAME(WEEKDAY,2) + ',' + DATENAME(WEEKDAY,3) + ',' + DATENAME(WEEKDAY,4) + ',' + DATENAME(WEEKDAY,5) + ',' + DATENAME(WEEKDAY,6)

Posted by andrei_solntsev on 18 April 2013

Nice code, however doesn't work correctly for contries where week starts on Monday. Would be nice to have an additional parameter for the first day of week.

Leave a Comment

Please register or log in to leave a comment.