CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Snowflake
Snowflake

Reputation: 3081

How to group by SQL based on certain conditions?

I am using Microsoft SQL Server 2014 and I have the following data about person watching Tv:

Where times are in minutes from midnight.

PersonId   StartTime  EndTime
1          300        600
1          250        700
1          200        800
1          900        1200
2          100        200

Now as you can see, what I want to have is that the viewing statements of person 1 is grouped as follows:

250 - 700 is overlapping with 300 - 600, so what I want to do is take the min from start time and max from end time because they overlap and I would like to do this for all viewing statements of person 1. But how can I do this with a group by?

Thanks

Upvotes: 0

Views: 72

Answers (1)

George T
George T

Reputation: 857

Gaps and Islands

DECLARE @TVWatchingTime TABLE (PersonId int, StartTime int, EndTime int)
INSERT INTO @TVWatchingTime 
VALUES 
(1,          300,        600),
(1,          250,        700),
(1,          200,        800),
(1,          900,        1200),
(2,          100,        200)

;WITH cteSource(PersonId, StartTime, EndTime)
AS 
(
    SELECT
        s.PersonId, s.StartTime, e.EndTime
    FROM        
    (
        SELECT  PersonId, StartTime, ROW_NUMBER() OVER (ORDER BY StartTime) AS rn
        FROM    @TVWatchingTime
    ) AS s
    INNER JOIN 
    (
        SELECT  PersonId, EndTime, ROW_NUMBER() OVER (ORDER BY EndTime) + 1 AS rn
        FROM    @TVWatchingTime
    ) AS e ON s.PersonId = e.PersonId AND e.rn = s.rn
    WHERE s.StartTime > e.EndTime

    UNION ALL

    SELECT PersonId, MIN(StartTime), MAX(EndTime)
    FROM    @TVWatchingTime
    GROUP BY PersonId
), cteGrouped(PersonId, theTime, grp)
AS (
    SELECT  PersonId, u.theTime,
        (ROW_NUMBER() OVER (ORDER BY u.theTime) - 1) / 2
    FROM    cteSource AS s
    UNPIVOT (
            theTime
            FOR theColumn IN (s.StartTime, s.EndTime)
        ) AS u
)
SELECT PersonId, MIN(theTime) StartTime, MAX(theTime) EndTime
FROM cteGrouped
GROUP BY PersonId, grp
ORDER BY PersonId, grp

Returns:

PersonId    StartTime   EndTime
1           200         800
1           900         1200
2           100         200

Upvotes: 5

Related Questions