DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;
 
CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;
 
WITH powers(powerN, n) AS (
SELECT POWER(2,number), number 
FROM master.dbo.spt_values 
WHERE type = 'P' AND number < 31)
 
INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11 
THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers
 
SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
, U.UserName AS SubscriptionCreator
,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
,CASE 
WHEN s.RecurrenceType = 1 THEN 'One Off'
WHEN s.RecurrenceType = 2 THEN 'Hour'
WHEN s.RecurrenceType = 4 THEN 'Daily'
WHEN s.RecurrenceType = 5 THEN 'Monthly' 
WHEN s.RecurrenceType = 6 THEN 'Week of Month' 
END AS RecurrenceType
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(SELECT ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [TEXT()] 
FROM #morepower m1 
WHERE m1.powerN < s.DaysofMonth+1 
AND s.DaysofMonth & m1.powerN <>0 
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
   , 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN CATALOG Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [TEXT()] 
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL 
AND m1.powerN & s1.MONTH <>0 
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [TEXT()] 
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL 
AND DaysOfWeek & m1.powerN <>0
AND  s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);
 
DROP TABLE #morepower;