How to transform this result set?

Question

I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below. Note that of the 4 rows that have a description of Staff Meeting, that they are 2 different events. One starting at 8:00 AM, and one starting at 10:00 AM. I have tried a few things, PIVOT , and a WHILE loop with no success. I was thinking of using a CURSOR, but I have not written one that advanced before. Any suggestions would be greatly appreciated.

Answer 1

The example below uses a CTE to find the gaps in your TimeScheduleId sequence. The field HasPreviousItem identifies if a row is the first in a sequence and RowNum is used to number all of the records. To find the start dates/times select for records where HasPreviousItem = 0.

The Duration assumes that each record is a 15 minute block of time. The cross apply subquery finds all records for the same ApptCode in the same block. That is records in sequence not interuppted by a HasPreviousItem value change. If the "duration" of a record is not always 15 minutes an alternate method to find the duration of an appointment would be needed.

CREATE TABLE ApptTemp (
     ProviderId INT NOT NULL DEFAULT 196
    , OfficeId INT NOT NULL DEFAULT 7
    , ApptCode CHAR(1) NOT NULL
    , Description VARCHAR(20) NOT NULL  
    , ScheduleTimeId INT NOT NULL
    , ScheduleTime DATETIME NOT NULL
    , ApptDate DATETIME NOT NULL
);

GO

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES ( 'L', 'Lunch', 49, '1900-01-01 12:00', '2014-03-20 12:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 50, '1900-01-01 12:15', '2014-03-20 12:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 51, '1900-01-01 12:30', '2014-03-20 12:30'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'L', 'Lunch', 52, '1900-01-01 12:45', '2014-03-20 12:45'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 33, '1900-01-01 08:00', '2014-03-20 08:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 34, '1900-01-01 08:15', '2014-03-20 08:15'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 41, '1900-01-01 10:00', '2014-03-20 10:00'  );

INSERT INTO ApptTemp ( ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate )
VALUES  ( 'S', 'Staff Meeting', 42, '1900-01-01 10:15', '2014-03-20 15:00'  );



WITH ApptCte 
AS (
SELECT ApptCode, Description, ScheduleTimeId, ScheduleTime, ApptDate, ProviderId, OfficeId

        , CASE
            WHEN  EXISTS
                (
                SELECT  *
                FROM     ApptTemp AS A2
                WHERE  A2.ScheduleTimeId = A1.ScheduleTimeId - 1
                    AND A2.ApptCode = A1.ApptCode
                )
                    THEN 1
            ELSE 0
        END AS HasPreviousItem 

        , RowNum = Row_Number() OVER(ORDER BY ScheduleTimeId)

FROM ApptTemp AS A1

)


SELECT 
     ApptDate
    , Description AS ApptDetail
    , OfficeId
    , ProviderId
    , Duration.Total AS Duration


FROM ApptCte AS MainCte
    CROSS APPLY
        (
            SELECT (COUNT(*) + 1) * 15 AS Total
            FROM ApptCte AS CountCte
            WHERE CountCte.ApptCode = MainCte.ApptCode
                AND CountCte.HasPreviousItem = 1
                AND CountCte.RowNum > MainCte.RowNum
                AND NOT EXISTS ( SELECT *
                                    FROM  ApptCte AS InnerCte 
                                    WHERE InnerCte.ApptCode = CountCte.ApptCode 
                                        AND InnerCte.HasPreviousItem = 0
                                        AND InnerCte.RowNum != MainCte.RowNum )
        ) AS Duration

WHERE HasPreviousItem = 0

ORDER BY ApptDate;