Determining Primary and Split Records

Split records are not differentiated in the table any differently than non-split records except for the fact that the Start_Time of a split record will equal the End_Time of the previous record. To determine whether a record is a split, non-split or the first record in a sequence of split records (for example, the primary downtime), a join can be made to the same table to check for the existence of a split.

SELECT	d1.Start_Time, 
        d1.End_Time,
        CASE WHEN d2.TEDet_Id IS NULL THEN 'Primary'
             ELSE 'Split' 
             END
FROM Timed_Event_Details d1
      LEFT JOIN Timed_Event_Details d2 ON d1.PU_Id = d2.PU_Id AND d1.Start_Time = d2.End_Time
WHERE	d1.PU_Id = @PUId
        AND d1.Start_Time < @ReportEndTime
        AND (	d1.End_Time > @ReportStartTime
                OR d1.End_Time IS NULL)