SQL - Create new start and stop datetime and combine rows based on other columns

Question

My issue is pretty similar to this question: Find start and end dates when one field changes

However, I've been unable to adapt the code to my situation. Any help would be greatly appreciated.

I have data which contains an ID, a variable called Type, and two datetime fields (one named Start and the other named Stop). This can be found here: http://sqlfiddle.com/#!6/35f43

Values for columns Start and Stop can run consecutively, and I want to combine those records grouping by ID and Type. The issue I currently have is I need to keep ID and Type separate if the Start and Stop datetimes are not consecutive.

For example, in the data on the SQLFiddle link, ID '3' currently has five records with type A with one record of type B in between.

ID         Start                                Stop               Type
3      2010-03-12 05:10:02.0000000    2010-03-20 12:00:00.0000000   A
3      2010-03-20 12:00:00.0000000    2010-04-07 09:46:30.0000000   A
3      2010-04-07 09:46:30.0000000    2010-05-01 08:13:42.0000000   B
3      2010-05-01 08:13:42.0000000    2010-07-04 09:30:32.0000000   A
3      2010-07-04 09:30:32.0000000    2010-07-06 11:41:38.0000000   A
3      2010-07-06 11:41:38.0000000    2010-07-09 12:51:30.0000000   A

I need this to show as:

ID         Start                                Stop               Type
3      2010-03-12 05:10:02.0000000    2010-04-07 09:46:30.0000000   A
3      2010-04-07 09:46:30.0000000    2010-05-01 08:13:42.0000000   B
3      2010-05-01 08:13:42.0000000    2010-07-09 12:51:30.0000000   A

Any ideas?

score 0 · Accepted Answer · answered Dec 04 '17 at 19:12

This will do it:

SELECT
    ID
    , Start
    , [Stop]
    , [Type]
FROM
    (
        SELECT
            ID
            , MIN(Start) Start
            , MAX([Stop]) [Stop]
            , [Type]
            , [Region]
        FROM
            (
                SELECT
                    ID
                    , Start
                    , [Stop]
                    , [Type]
                    , SUM([First]) OVER (PARTITION BY ID ORDER BY [Start]) Region
                FROM
                    (
                        SELECT
                            ID
                            , Start
                            , [Stop]
                            , [Type]
                            ,
                                CASE
                                    WHEN LAG([Type], 1, NULL) OVER (PARTITION BY ID ORDER BY [Start]) = [Type]
                                    THEN 0
                                    ELSE 1
                                END [First]
                        FROM YourTable
                    ) Q
            ) Q2
        GROUP BY
            ID
            , [Type]
            , [Region]
    ) Q3
ORDER BY Region

score 0 · Answer 2 · answered Dec 04 '17 at 23:13

;WITH CTE_T AS (
    select *,ROW_NUMBER() OVER(ORDER BY Start) r 
        from T A 
            where not exists(
                select * from T B 
                    WHERE B.ID = A.ID AND B.Start = A.Stop AND B.Type = A.Type
                )
    UNION ALL
    SELECT B.*,A.R fROM CTE_T A 
        JOIN T B ON A.Start = B.Stop 
            AND B.ID = A.ID  AND B.Type = A.Type
)

SELECT ID,MIN(Start) [Start],MAX(Stop) [Stop],Type FROM CTE_T
GROUP BY ID,TYPE,R

SQL - Create new start and stop datetime and combine rows based on other columns

2 Answers2