Solution to TSQL Challenge 35 - Find the total number of 'Full Attendees' in each 24 HOP Session By malpashaa
WITH SessionsCTE AS
(
SELECT S.SessionID, S.Speaker, S.Topic,
S.Schedule AS introduction_start_date, DATEADD(MINUTE, 5, S.Schedule) AS introduction_end_date,
DATEADD(MINUTE, 5, S.Schedule) AS main_session_start_date, DATEADD(MINUTE, 45, S.Schedule) AS main_session_end_date,
DATEADD(MINUTE, 45, S.Schedule) AS QA_session_start_date, DATEADD(MINUTE, 55, S.Schedule) AS QA_session_end_date,
DATEADD(MINUTE, 55, S.Schedule) AS thank_you_note_start_date, DATEADD(MINUTE, 60, S.Schedule) AS thank_you_note_end_date
FROM (SELECT S.SessionID, S.Speaker, S.Topic,
DATEADD(MINUTE,
CAST(SUBSTRING(TZ.TimeDiff, 4, 1) + '1' AS INT) * -1 * (SUBSTRING(TZ.TimeDiff, 5, 2) * 60 + SUBSTRING(TZ.TimeDiff, 7, 2)),
CONVERT(DATETIME, SUBSTRING(S.Schedule, 1, 16), 120)) AS Schedule
FROM TC35_TimeZones AS TZ
INNER JOIN
TC35_Sessions AS S
ON SUBSTRING(S.Schedule, 18, 2147483647) = TZ.TimeZone) AS S
)
, AttendeesCTE AS
(
SELECT A.AttendeeID, A.AttendeeName, A.ActionType, A.ActionTime
FROM (SELECT A.AttendeeID, A.AttendeeName, A.ActionType,
DATEADD(MINUTE,
CAST(SUBSTRING(TZ.TimeDiff, 4, 1) + '1' AS INT) * -1 * (SUBSTRING(TZ.TimeDiff, 5, 2) * 60 + SUBSTRING(TZ.TimeDiff, 7, 2)),
CONVERT(DATETIME, SUBSTRING(A.ActionTime, 1, 16), 120)) AS ActionTime
FROM TC35_TimeZones AS TZ
INNER JOIN
TC35_Attendees AS A
ON SUBSTRING(A.ActionTime, 18, 2147483647) = TZ.TimeZone) AS A
)
, AttendeesIntervalsCTE AS
(
SELECT A1.AttendeeID, A1.ActionTime AS start_date, ISNULL(A2.ActionTime, '99991231') AS end_date
FROM (SELECT A1.AttendeeID, A1.ActionTime, ROW_NUMBER() OVER(PARTITION BY A1.ActionType, A1.AttendeeID ORDER BY A1.ActionTime) AS row_num
FROM AttendeesCTE AS A1
WHERE A1.ActionType = 'login') AS A1
LEFT OUTER JOIN
(SELECT A2.AttendeeID, A2.ActionTime, ROW_NUMBER() OVER(PARTITION BY A2.ActionType, A2.AttendeeID ORDER BY A2.ActionTime) AS row_num
FROM AttendeesCTE AS A2
WHERE A2.ActionType = 'logout') AS A2
ON A2.AttendeeID = A1.AttendeeID
AND A2.row_num = A1.row_num
)
, FullAttendeesCTE AS
(
SELECT S.Speaker, S.Topic,
CASE WHEN SUM(CASE WHEN A.end_date >= S.introduction_start_date
AND A.start_date < S.introduction_end_date
THEN DATEDIFF(MINUTE, CASE WHEN A.start_date < S.introduction_start_date
THEN S.introduction_start_date
ELSE A.start_date
END,
CASE WHEN A.end_date > S.introduction_end_date
THEN S.introduction_end_date
ELSE A.end_date
END)
ELSE 0
END) >= 2
AND SUM(CASE WHEN A.end_date >= S.main_session_start_date
AND A.start_date < S.main_session_end_date
THEN DATEDIFF(MINUTE, CASE WHEN A.start_date < S.main_session_start_date
THEN S.main_session_start_date
ELSE A.start_date
END,
CASE WHEN A.end_date > S.main_session_end_date
THEN S.main_session_end_date
ELSE A.end_date
END)
ELSE 0
END) >= 10
AND SUM(CASE WHEN A.end_date >= S.QA_session_start_date
AND A.start_date < S.QA_session_end_date
THEN DATEDIFF(MINUTE, CASE WHEN A.start_date < S.QA_session_start_date
THEN S.QA_session_start_date
ELSE A.start_date
END,
CASE WHEN A.end_date > S.QA_session_end_date
THEN S.QA_session_end_date
ELSE A.end_date
END)
ELSE 0
END) >= 5
AND SUM(CASE WHEN A.end_date >= S.thank_you_note_start_date
AND A.start_date < S.thank_you_note_end_date
THEN DATEDIFF(MINUTE, CASE WHEN A.start_date < S.thank_you_note_start_date
THEN S.thank_you_note_start_date
ELSE A.start_date
END,
CASE WHEN A.end_date > S.thank_you_note_end_date
THEN S.thank_you_note_end_date
ELSE A.end_date
END)
ELSE 0
END) >= 1
THEN 1
ELSE 0
END AS is_full_attendee
FROM SessionsCTE AS S
LEFT OUTER JOIN
AttendeesIntervalsCTE AS A
ON A.end_date >= S.introduction_start_date
AND A.start_date < S.thank_you_note_end_date
GROUP BY S.Speaker, S.Topic, A.AttendeeID
)
SELECT FA.Speaker, FA.Topic, SUM(FA.is_full_attendee) AS [Attendee Count]
FROM FullAttendeesCTE AS FA
GROUP BY FA.Speaker, FA.Topic
ORDER BY FA.Speaker, FA.Topic;
Sr# StartTime Reads Writes CPU Duration
--- ------------------- ----------- ----------- ----------- --------
1 Dec 10 2010 5:43PM 20418 49 5569 7.552
2 Dec 10 2010 5:56PM 20415 42 5616 7.668
3 Dec 10 2010 6:09PM 20418 49 5538 7.528
4 Dec 10 2010 6:22PM 20415 42 5601 7.337
5 Dec 10 2010 6:35PM 20418 49 5662 8.130