CODEWITHSUNDEEP
sqlsql-serverselect
Laurens Wolf
Laurens Wolf

Reputation: 163

Grouping date periods in SQL Server

I run this query in SQL Server:

select 
    personeel_nummer as employee_number,
    vanaf_datum as from_date,
    tot_datum as until_date
from 
    personeel_kenmerken
where 
    personeel_nummer = 32600

And I get this as result:

employee_number from_date until_date
32600 2022-10-01 2023-02-26
32600 2023-02-27 2023-09-03
32600 2023-09-04 2023-09-18
32600 2023-10-10 2023-10-18
32600 2023-10-19 null

I now need a query to see the periods this employee has been with us 2 periods: from 2022-10-01 until 2023-09-18 because all the until_dates and from dates_ are adjacent between them and from 2023-10-10 until null as the 2nd period (null means infinite in our case)

Expected result:

employee_number from_date until_date
32600 2022-10-01 2023-09-18
32600 2023-10-10 null

Can someone help me write a select query that can generate this result?

Thanks in advance!

Upvotes: 0

Views: 93

Answers (2)

Dileep Kare
Dileep Kare

Reputation: 81

Try this below query

 WITH Cte AS (
    SELECT
        personeel_nummer,
        vanaf_datum,
        tot_datum,
        CASE WHEN vanaf_datum = DATEADD(DAY, 1, LAG(tot_datum) OVER (ORDER BY personeel_nummer, vanaf_datum)) THEN ROW_NUMBER() OVER (ORDER BY personeel_nummer, vanaf_datum) ELSE 0 END AS GroupChange
    FROM
        personeel_kenmerken
),
Cte2 AS (
    SELECT
        personeel_nummer,
        vanaf_datum,
        tot_datum,
        GroupChange,
        ISNULL(LEAD(GroupChange) OVER (ORDER BY personeel_nummer, vanaf_datum), 0) AS a
    FROM
        Cte
),
Cte3 AS (
    SELECT
        personeel_nummer,
        vanaf_datum,
        tot_datum,
        CASE WHEN GroupChange = 0 THEN vanaf_datum ELSE NULL END AS from_date,
        CASE WHEN a = 0 THEN tot_datum ELSE NULL END AS to_date
    FROM
        Cte2
),
cte4 as
(SELECT personeel_nummer employee_number
    ,MAX(from_date) OVER (PARTITION BY personeel_nummer ORDER BY vanaf_datum) AS from_date,
    to_date

FROM
    Cte3)
    select * from cte4 where to_date is not null

Upvotes: 0

PeterClemmensen
PeterClemmensen

Reputation: 4937

Try this

DROP TABLE IF EXISTS #t;

CREATE TABLE #t
(
   employee_number  INT 
 , from_date        DATE
 , until_date       DATE
)
;

INSERT INTO #t (employee_number, from_date, until_date)
VALUES
  (32600,  '2022-10-01', '2023-02-26')
, (32600,  '2023-02-27', '2023-09-03')
, (32600,  '2023-09-04', '2023-09-18')
, (32600,  '2023-10-10', '2023-10-18')
, (32600,  '2023-10-19', NULL        )
, (32601,  '2022-10-01', '2023-02-26')
, (32601,  '2023-02-27', '2023-09-03')
, (32601,  '2023-09-04', '2023-09-18')
, (32601,  '2023-10-10', '2023-10-18')
, (32601,  '2023-10-19', NULL        )
;

WITH C1 AS (
  SELECT *, 
         LAG(until_date) OVER (PARTITION BY employee_number ORDER BY from_date) AS lag_until_date
  FROM #t
),

C2 AS (
  SELECT employee_number,
         from_date,
         until_date,
         SUM(CASE WHEN from_date = DATEADD(day, 1, lag_until_date) 
             THEN 0 ELSE 1 END) 
             OVER (PARTITION BY employee_number ORDER BY from_date) AS grp
  FROM C1
)

SELECT employee_number,
       MIN(from_date) AS from_date,
       NULLIF(MAX(COALESCE(until_date, '12/31/2099')),'12/31/2099') AS until_date
FROM C2
GROUP BY employee_number, grp
ORDER BY employee_number, from_date
;

Result:

employee_number from_date   until_date
32600           2022-10-01  2023-09-18
32600           2023-10-10  NULL
32601           2022-10-01  2023-09-18
32601           2023-10-10  NULL

Upvotes: 2

Related Questions