Sql server min and max dates between 2 columns and multiple rows

Question

In Sqlserver 2019 script, I have a table for employee leaves like below format For example employee has consumed his annual leave days as mentioned below

Empcode  LeaveFrom    LeaveTo
A        23/07/2023    01/08/2023
         02/08/2023    03/08/2023
         04/08/2023    04/08/2023



B        10/07/2023    01/08/2023
         02/08/2023    03/08/2023

expected Output result Ecode Leave from Leave to A 23/07/2023 04/08/2023
B 10/07/2023 03/08/2023

I have used lead function, but still I didn't get what I expected result. looking for suggestions.

I have tried lead function, row_number(), rank, dense_rank

Answer 1

I didn’t got you but you can try this:)

SELECT Empcode, MIN(LeaveDate) AS MinLeaveDate, MAX(LeaveDate) AS MaxLeaveDate
FROM (
    SELECT Empcode, LeaveFrom AS LeaveDate FROM Leaves
    UNION
    SELECT Empcode, LeaveTo AS LeaveDate FROM Leaves
) AS CombinedLeaves
GROUP BY Empcode;