09 April 2013

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!

2 comments:

  1. Dear friend. I truly just like your posting and your current web page all in all! That write-up is really plainly composed and without difficulty understandable. Your Wordpress style is awesome as well! Would be awesome to discover exactly where My partner and i are able obtain this. Please hold up the very good work. We all need much more this kind of website owners just like you on the web and much less spammers. Wonderful man!
    http://www.sqlservermasters.com/

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri