Daily Calendar Events Placed By Time

• postald11

  SSC-Addicted

  Points: 455

  More actions

  November 16, 2015 at 8:44 am

  #322661

  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!