Have you ever wondered why Dynamics CRM Service calendar doesn’t show all CRM activities but only Appointments and Service Activities? Here is the report that can help you a lot when you plan your tasks, phone calls and etc. Link to the file The source code:
–DECLARE @StartDate DATETIME, @EndDate DATETIME SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) –FirstDayOfMonth SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) –LastDayOfMonth ; WITH Months AS ( SELECT [Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))), [Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))), FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)), LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)), FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))), LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))) UNION ALL SELECT [Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)), [Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)), FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth), LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)), FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)), LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))) FROM Months WHERE LastDayOfMonth < @EndDate ), Dates AS ( SELECT [Month], [Year], [Date] = FirstDayOfCalendar, FilterDate = LastDayOfCalendar FROM Months UNION ALL SELECT [Month], [Year], [Date] = DATEADD(DAY,1,[Date]), FilterDate FROM Dates WHERE [Date] < FilterDate ), ActivityName AS ( SELECT scheduledend, [Day] = DATEPART(DAY, scheduledend), [Month] = DATEPART(MONTH, scheduledend), [Year] = DATEPART(YEAR, scheduledend), Subject, Activityid, Activitytypecode FROM FilteredActivityPointer AS CRMAF_FilteredActivityPointer ) SELECT DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month), d.Month, [Day] = DATEPART(DAY,d.[Date]), d.Year, [WeekDay] = DATEPART(WEEKDAY, d.[Date]), [Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date), d.Date, aname.Subject, aname.activityid, aname.activitytypecode FROM Dates d LEFT JOIN ActivityName aname ON aname.Year = DATEPART(YEAR,d.[Date]) AND aname.Month = DATEPART(MONTH,d.[Date]) AND aname.Day = DATEPART(DAY,d.[Date]) OPTION (MAXRECURSION 1000)
I used the idea by Ryan Duclos – and updated the report to work correctly with Dynamics CRM SSRS 2008 Generate Calendar(s) based on a date range « Ryan Duclos