get dates periods-CodePudding

it is necessary to get the date periods. input data:

     login    date_start    date_end    code
    'user1', '2022-02-09', '2022-02-09'  DO
    'user1', '2022-02-10', '2022-02-10'  DO
    'user1', '2022-02-11', '2022-02-11'  DO
    'user1', '2022-03-28', '2022-03-28'  OT
    'user1', '2022-03-29', '2022-03-29'  OT
    'user1', '2022-03-30', '2022-03-30'  OT
    'user1', '2022-03-31', '2022-03-31'  OT
    'user1', '2022-04-01', '2022-04-01'  OT
    'user1', '2022-04-04', '2022-04-04'  DO

expected result:

login  date_start  date_end   
user1  2022-02-09  2022-02-11 
user1  2022-03-28  2022-04-01
user1  2022-04-04  2022-04-04

CodePudding user response：

This is a gaps and islands problem. One trick we can use here is to create a pseudo-group which tracks to which island of continuous dates each record belongs.

WITH cte AS (
    SELECT *, CASE WHEN LAG(date_start, 1, date_start) OVER
                        (PARTITION BY login ORDER BY date_start) =
                        DATEADD(day, -1, date_start)
                   THEN 0 ELSE 1 END AS cnt
    FROM yourTable
),
cte2 AS (
    SELECT *, SUM(cnt) OVER (PARTITION BY login ORDER BY date_start) AS grp
    FROM cte
)

SELECT login, MIN(date_start) AS date_start, MAX(date_end) AS date_end
FROM cte2
GROUP BY login, grp
ORDER BY MIN(date_start);

Demo

In the first CTE above, the alias cnt gets assigned to a value of 1 whenever the preceding date is not one day earlier than the current date. We can then sum over this column and obtain groups of dates forming a continuous range.

CodePudding user response：

like this

select max(date_end) date_end , min(date_start) date_start, login from table group by login, code