November 16, 2015 at 8:44 am
I have a calendar report that pulls events for each day of a month. I'm wanting to have a column with cells that displays 12AM-11PM for each day. Based on an event's start and end time, I want the event to be placed within that range accordingly. Below is my current output, desired output, and current stored procedure populating the current output:
Current Output: http://tinypic.com/view.php?pic=30tl6rp&s=9#.VkZbJ1WrRhE
Current Stored Proc:
ALTER PROCEDURE [Event].[Report_Event_Calendar_Day_All_sp]
@start_cymd DATE = NULL
AS
BEGIN
DECLARE @StartDate DATE = @start_cymd
;WITH Dates([Date])
AS (
SELECT @StartDate AS [Date]
UNION ALL
SELECT DATEADD(DAY, 1, [Date])
FROM Dates
WHERE [Date] < @StartDate
) ,
Events
AS (
SELECT EventDesc ,
start_cymd AS EventDate ,
end_cymd ,
Notes ,
MemberName ,
All_Day ,
CONVERT(CHAR(15), CAST(StartTime AS TIME), 100) AS StartTime ,
CONVERT(CHAR(15), CAST(EndTime AS TIME), 100) AS EndTime
FROM [Event].Event_Description ED
INNER JOIN [Event].SQL_Team_Events SE ON ED.EventDesc_ID = SE.EventDesc_ID
INNER JOIN [Event].SQL_Team_Member SM ON SE.Event_ID = SM.Event_ID
INNER JOIN [Event].Members M ON SM.Member_ID = M.Member_ID
) ,
Holidays
AS (
SELECT CalendarDate ,
Holiday ,
CalendarDateDescription
FROM Toolbox.dbo.Calendar
)
---Number the records based on the date, if multiple records have
---the same date then they will be numbered the same. Used in
---calculation to determine row record is to display
SELECT [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]) ,
---Date used in all calculations for date
d.[Date] ,
---Generates matrix columns
[WeekDay] = DATEPART(WEEKDAY, d.[Date]) ,
---Used to display day of month on calendar
[Day] = DATEPART(DAY, d.[Date]) ,
---Used in some calculations for display
[Month] = DATEPART(MONTH, d.[Date]) ,
e.EventDesc ,
e.Notes ,
e.EventDate,
e.end_cymd ,
e.All_Day ,
e.MemberName ,
e.StartTime ,
e.EndTime ,
h.Holiday ,
h.CalendarDateDescription
FROM Dates d
LEFT JOIN Events e ON d.[Date] = e.EventDate
LEFT JOIN Holidays h ON d.[Date] = h.CalendarDate
END
GO
Desired Output: http://tinypic.com/view.php?pic=vfwgpi&s=9#.VkZbtVWrRhF
Does anyone have any ideas on how I could use SSRS to leverage this requirement? Thanks!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply