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 @MonthASINT = 4 --Set the MONTHfor which you want to generate the Calendar.

DECLARE @YearASINT = 2013 --Set the YEARfor which you want to generate the Calendar.

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