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
