[SQL] How to merge row based on condition in SQL Server

I have table in sqlserver data base with name department and it has 4 columns.

staffNumber department startDate endDate
100 A 2016-09-22 18:30:00.000 2020-02-06 18:29:00.000
100 B 2020-02-06 18:30:00.000 2022-08-21 18:29:00.000
100 A 2022-08-21 18:30:00.000 2079-12-31 18:29:00.000
101 A 2018-06-03 18:30:00.000 2023-08-20 18:29:00.000
101 B 2023-08-20 18:30:00.000 2079-12-31 18:29:00.000
102 A 2022-12-21 18:30:00.000 2023-12-29 18:29:00.000
102 A 2023-12-29 18:30:00.000 2079-12-31 18:29:00.000
103 A 2016-06-22 18:30:00.000 2018-03-05 18:29:00.000
103 A 2018-03-05 18:30:00.000 2021-03-05 18:29:00.000
103 A 2021-03-05 18:30:00.000 2079-12-31 18:29:00.000
104 A 2016-06-22 18:30:00.000 2021-03-05 18:29:00.000
104 A 2021-03-05 18:30:00.000 2079-12-31 18:29:00.000
105 B 2016-08-22 18:30:00.000 2021-02-07 18:29:00.000
105 B 2021-02-09 18:30:00.000 2079-12-31 18:29:00.000

I want to merge more then rows based on specific conditions [same staffNumber, same department, and consecutive records have zero day difference between the expiryDate of the current row and the effectiveDate of the next row]

below is SQL quey i tried but i am not getting expected output

SQL-Server Code

WITH DeptData_CTE AS (
SELECT
staffNumber,
department,
startDate,
endDate
LEAD(startDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate) AS nextstartDate,
LAG(endDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate) AS prevendDate
FROM department
),
MergedData_CTE AS (
SELECT
staffNumber,
department,
MIN(startDate) AS startDate,
MAX(endDate) AS endDate
FROM DeptData_CTE
-- We merge rows where previous expiry date and current effective date are consecutive
WHERE prevendDate IS NULL
OR DATEDIFF(day, prevendDate, startDate) != 0
GROUP BY
staffNumber,
department
)
-- Final selection of rows
SELECT
staffNumber,
department,
startDate,
endDate
FROM MergedData_CTE
ORDER BY
staffNumber,
department,
startDate;


I am getting expected satrtDate with mearged rows with two consecutive row but not getting expected endDate.

My Expected output is

staffNumber department startDate endDate
100 A 2016-09-22 18:30:00.000 2020-02-06 18:29:00.000
100 B 2020-02-06 18:30:00.000 2022-08-21 18:29:00.000
100 A 2022-08-21 18:30:00.000 2079-12-31 18:29:00.000
101 A 2018-06-03 18:30:00.000 2023-08-20 18:29:00.000
101 B 2023-08-20 18:30:00.000 2079-12-31 18:29:00.000
102 A 2022-12-21 18:30:00.000 2079-12-31 18:29:00.000
103 A 2016-06-22 18:30:00.000 2079-12-31 18:29:00.000
104 A 2016-06-22 18:30:00.000 2079-12-31 18:29:00.000
105 B 2016-08-22 18:30:00.000 2021-02-07 18:29:00.000
105 B 2021-02-09 18:30:00.000 2079-12-31 18:29:00.000

for staff 105 row is not mearged because date difference is >= 1 day

I do not want to use Stored procedure. I Just want to use SELECT SQL query

Continue reading...