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

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

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!

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...