SQL Event Calendar

Question

I have an Event Calendar that I am trying to create in SQL.The issue is I can not pivot the events like I do the dates. Is there a way to do two pivots or something similiar so that the events line up under the dates.

Here is the table before a pivot

YearNum MonthName   week_number DayNum  DayofWeek   EventStart  EventSubject    EventLocation   EventDate
2018    August                  33  14  Tuesday         NULL    NULL    NULL    NULL
2018    August                  33  15  Wednesday       NULL    NULL    NULL    NULL
2018    August                  33  16  Thursday        13:00   Dan's Birthday  EC-E    2018-08-16
2018    August                  33  17  Friday          NULL    NULL    NULL    NULL
2018    August                  33  18  Saturday        NULL    NULL    NULL    NULL
2018    August                  34  19  Sunday          NULL    NULL    NULL    NULL
2018    August                  34  20  Monday          NULL    NULL    NULL    NULL

After the pivot, this is how it looks

Here is the SQL Query that I am currently working with:

SELECT MonthName, YearNum, Sunday, Monday,Tuesday,Wednesday,Thursday,Friday,Saturday, EventStart, EventSubject, EventLocation, EventDate
FROM 
(
SELECT        CalendarDates.YearNum, CalendarDates.MonthName, DATEPART(WEEK, CalendarDates.StandardDate) AS week_number, CalendarDates.DayNum, 
                         CalendarDates.DayofWeek, EventList.EventStart, EventList.EventSubject, EventList.EventLocation, EventList.EventDate
FROM            (SELECT        EventStart, EventSubject, EventLocation, EventDate
                          FROM            EventList AS EventList_1) AS EventList RIGHT OUTER JOIN
                         CalendarDates ON EventList.EventDate = CalendarDates.StandardDate
WHERE        (CalendarDates.MonthNum = '8') AND (CalendarDates.YearNum = '2018')
)
pivotDates
PIVOT (MIN(DayNum) FOR DayofWeek IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)) AS Pivots

This is how the output should look like

Answer 1

This problem could be better solved in the presentation layer, as the SQL does a good job on computing/filtering data, but not in user interface.

Anyways, try this:

DECLARE @CurrentMonth   INT = 8,
        @CurrentYear    INT = 2018

;WITH   RawData AS  -- Your original appointment dates
(
        SELECT  CAST('2018-08-16' AS DATE)  Date, 
                CAST('13:00'      AS TIME)  Start, 
                'Someone birthday'          Subject, 
                'Location'                  Location 
),      AllMonthDays AS -- list of all days in the filtered month
(
        SELECT  CAST(CAST(@CurrentYear  AS VARCHAR) + '-' + 
                     CAST(@CurrentMonth AS VARCHAR) + '-01' AS DATE) Date UNION ALL
        SELECT  DATEADD(D, +1, Date)
        FROM    AllMonthDays
        WHERE   MONTH(DATEADD(D, +1, Date)) = @CurrentMonth
),      CalendarData AS -- Adds the weekday name, week numbering and appointment data
(
        SELECT      AllMonthDays.Date,
                    DATEPART(WEEK   , AllMonthDays.Date) WeekNumber,
                    DATENAME(DW     , AllMonthDays.Date) WeekdayName,
                    NULLIF(CONCAT(
                        CAST(RawData.Start AS CHAR(5)), ' ', 
                        RawData.Subject, '@ ', RawData.Location), ' @ ') Appointment
        FROM        AllMonthDays
        LEFT JOIN   RawData
                ON  RawData.Date = AllMonthDays.Date
),      CalendarFormat AS   -- PIVOT the data, putting the weekdays as columns
(
        SELECT  *
        FROM    CalendarData 
        PIVOT
        (   --MIN(Date) FOR         -- uncomment this to a better understanding
            MIN(Appointment) FOR    -- weird part: using MIN in a text column
            WeekdayName IN
            (   [Sunday], [Monday], [Tuesday],
                [Wednesday], [Thursday], [Friday], [Saturday]
            )
        ) p
)
SELECT      MIN(WeekNumber ) [WeekNumber],
            MIN([Sunday]   ) [Sunday], 
            MIN([Monday]   ) [Monday], 
            MIN([Tuesday]  ) [Tuesday], 
            MIN([Wednesday]) [Wednesday], 
            MIN([Thursday] ) [Thursday], 
            MIN([Friday]   ) [Friday], 
            MIN([Saturday] ) [Saturday]
FROM        CalendarFormat
GROUP BY    WeekNumber  -- suppress duplications
ORDER BY    WeekNumber