SQL Reloaded: TSQL Challenge 24

     Beyond Relational came up with an interesting set of  TSQL Challenges contest that reached already its 25th round. Here’s my answer for TSQL Challenge 24, this being the first time I’ve attempted to come with a solution:

 

WITH CTE(LogID, EmpName, Activity, StartTime, TopStartTime, NoMinutes, StartDuration, DiffDuration, HasParentFlag, HasChildFlag, Level) 
AS ( -- initialization query
SELECT A.LogID, A.EmpName, A.Activity, A.StartTime
, A.StartTime TopStartTime
, DATEPART(mi, A.Duration) NoMinutes
, DATEPART(mi, A.Duration) StartDuration
, DAT.NoMinutes DiffDuration
, IsNull(DAT.HasParentFlag, 0) HasParentFlag
, ISNULL(DAT.HasChildFlag, 0) HasChildFlag
, 1 Level
FROM TC24_ActivityLog A
LEFT JOIN TC24_ActivityLog B
ON A.EmpName = B.EmpName
AND A.Activity = B.Activity
AND DateDiff(Mi, B.StartTime, A.StartTime) BETWEEN 0 AND 30
AND DateDiff(dd, B.StartTime, A.StartTime)= 0
AND A.LogID <> B.LogID
CROSS APPLY ( -- cumulate
     SELECT SUM(DATEPART(mi, C.Duration)) NoMinutes
     , Max(CASE WHEN D.EmpName IS NOT NULL THEN 1 ELSE 0 END) HasParentFlag
     , Max(CASE WHEN E.EmpName IS NOT NULL THEN 1 ELSE 0 END) HasChildFlag
     , Max(CASE WHEN D.EmpName IS NULL AND D.EmpName IS NULL THEN 1 ELSE 0 END)   IsSingleFlag
      FROM TC24_ActivityLog C
        LEFT JOIN TC24_ActivityLog D
          ON C.EmpName = D.EmpName
        AND C.Activity = D.Activity
        AND DateDiff(Mi, D.StartTime, C.StartTime)=30
        AND DateDiff(dd, D.StartTime, C.StartTime)= 0
        LEFT JOIN TC24_ActivityLog E
            ON C.EmpName = E.EmpName
         AND C.Activity = E.Activity
         AND DateDiff(Mi, E.StartTime, C.StartTime)=-30
         AND DateDiff(dd, E.StartTime, C.StartTime)= 0
        WHERE A.EmpName = C.EmpName
         AND A.Activity <> C.Activity
        AND DateDiff(Mi, C.StartTime, A.StartTime)=0) DAT
WHERE B.EmpName IS NULL
UNION ALL
SELECT A.LogID, A.EmpName, A.Activity, A.StartTime
, B.TopStartTime
, B.NoMinutes + DATEPART(mi, A.Duration) NoMinutes
, B.StartDuration
, B.DiffDuration
, B.HasParentFlag
, B.HasChildFlag
, B.Level+1 Level
FROM TC24_ActivityLog A
JOIN CTE B
ON A.EmpName = B.EmpName
AND A.Activity = B.Activity
AND DateDiff(Mi, B.StartTime, A.StartTime)=30)
SELECT
Convert(varchar(10), TCS.StartTime, 126) [Date]
, TCS.EmpName
, TCS.Activity
, Convert(varchar(5), TCS.StartTime, 108) SchSt
, Convert(varchar(5), TCS.Duration, 108) SchDur
, Convert(varchar(5), DateAdd(mi, CASE
WHEN HasParentFlag = 1 THEN TCA.DiffDuration
WHEN HasChildFlag = 1 THEN 0
ELSE 30-TCA.StartDuration
END, TCA.TopStartTime), 108) ActSt --
, Convert(varchar(5), DateAdd(mi, TCA.NoMinutes, Cast('00:00' as time(0))), 108) ActDur
FROM TC24_Schedules TCS
JOIN (-- Activities Log
      SELECT EmpName
      , Activity
      , StartTime
      , TopStartTime
      , NoMinutes
      , StartDuration
     , DiffDuration
      , HasParentFlag
      , HasChildFlag
      , RANK() OVER(PARTITION BY EmpName, Activity, TopStartTime ORDER BY Level DESC) Ranking
     FROM CTE
) TCA
     ON TCS.EmpName = TCA.EmpName
    AND TCS.Activity = TCA.Activity
    AND DateDiff(d, TCS.StartTime, TCA.TopStartTime) = 0
    AND TCA.Ranking = 1
ORDER BY [Date]
, EmpName
, SchSt;

Notes:
1.    The initialization query identifies the top-most activities and checks whether there is another activity (referred as co-occurring activity) scheduled within the same 30 minutes time interval by using the CROSS APPLY operator, therefore:
DiffDuration – stores the time scheduled within the same 30 minutes for the co-occurring activity
HasParentFlag – has the value 1 if the co-occurring activity is a continuation from the previous 30 minutes, 0 otherwise
HasChildFlag – has the value 1 if the co-occurring activity continues on the next 30 minutes, 0 otherwise
DiffDuration, HasParentFlag, HasChildFlag are needed in order to calculate correctly the ActSt.
   The recursive query just aggregates the total time, the final output query considering the lowest level in the recursion tree (the end activity).
   The query works also with the Example 1 & 2.
   The query was also tested using Lutz Mueller's test data set.
  
2.  By adding the rank function for Schedule table (e.g. RANK() OVER(PARTITION BY EmpName, Activity ORDER BY StartTime) SchRanking) thus creating an inline view,
    a dense rank in the final Activites subquery (e.g.   , DENSE_RANK() OVER(PARTITION BY EmpName, Activity ORDER BY TopStartTime) SchRanking
    and matching the result sets on the two attributes (e.g. AND TCS.SchRanking = TCA.SchRanking) the query could be leveraged to handle interrupted activites (activities interrupted by another activity, FAQ11).