SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table dates (ID int, StartDate datetime2(0), EndDate datetime2(0), TaskID int, EmployeeID int)

insert into dates (ID, StartDate, EndDate, TaskID, EmployeeID) values 
(1,   '2023-11-1 20:30:00',  '2023-11-1 23:30',        2,                  1),
(2,   '2023-11-1 21:30:00',  '2023-11-1 23:30',        2,                  2),
(3,   '2023-11-1 20:30:00',  '2023-11-2 01:30',        2,                  3),
(4,   '2023-11-2 20:30:00',  '2023-11-2 23:30',        2,                  1),
(5,   '2023-11-2 20:30:00',  '2023-11-2 23:30',        2,                  2),
(6,   '2023-11-2 20:30:00',  '2023-11-3 00:30',        2,                  3)

select * from dates


ID
StartDate
EndDate
TaskID
EmployeeID


1
2023-11-01 20:30:00
2023-11-01 23:30:00
2
1

2
2023-11-01 21:30:00
2023-11-01 23:30:00
2
2

3
2023-11-01 20:30:00
2023-11-02 01:30:00
2
3

4
2023-11-02 20:30:00
2023-11-02 23:30:00
2
1

5
2023-11-02 20:30:00
2023-11-02 23:30:00
2
2

6
2023-11-02 20:30:00
2023-11-03 00:30:00
2
3


select d.ID, d.StartDate, d.EndDate, d.TaskID, d.EmployeeID from dates d
where  convert(date, d.startdate) = convert(date, d.enddate)
union all
select d.ID, d.StartDate, convert(datetime, convert(date, d.StartDate)), d.TaskID, d.EmployeeID 
from dates d
where  convert(date, d.startdate) < convert(date, d.enddate)
union all
select d.ID, convert(datetime, convert(date, d.EndDate)), d.EndDate, d.TaskID, d.EmployeeID 
from dates d
where  convert(date, d.startdate) < convert(date, d.enddate)

order by d.ID


ID
StartDate
EndDate
TaskID
EmployeeID


1
2023-11-01 20:30:00.000
2023-11-01 23:30:00.000
2
1

2
2023-11-01 21:30:00.000
2023-11-01 23:30:00.000
2
2

3
2023-11-01 20:30:00.000
2023-11-01 00:00:00.000
2
3

3
2023-11-02 00:00:00.000
2023-11-02 01:30:00.000
2
3

4
2023-11-02 20:30:00.000
2023-11-02 23:30:00.000
2
1

5
2023-11-02 20:30:00.000
2023-11-02 23:30:00.000
2
2

6
2023-11-03 00:00:00.000
2023-11-03 00:30:00.000
2
3

6
2023-11-02 20:30:00.000
2023-11-02 00:00:00.000
2
3


select t.ID,
       t.StartDate,
       t.EndDate,
       t.TaskID,
       t.EmployeeID,
       DATEDIFF(mi, DATEADD(hh, DATEDIFF(mm, t.StartDate, t.EndDate), t.StartDate), t.EndDate) as Mins
from   ( select d.ID, d.StartDate, d.EndDate, d.TaskID, d.EmployeeID
         from dates d
         where  convert(date, d.startdate) = convert(date, d.enddate)
         union all
         select d.ID, d.StartDate, dateadd(dd, 1, convert(datetime, convert(date, d.StartDate))), d.TaskID, d.EmployeeID
         from dates d
         where  convert(date, d.startdate) < convert(date, d.enddate)
         union all
         select d.ID, convert(datetime, convert(date, d.EndDate)), d.EndDate, d.TaskID, d.EmployeeID
         from dates d
         where  convert(date, d.startdate) < convert(date, d.enddate)
       ) t
order by t.ID, t.StartDate


ID
StartDate
EndDate
TaskID
EmployeeID
Mins


1
2023-11-01 20:30:00.000
2023-11-01 23:30:00.000
2
1
180

2
2023-11-01 21:30:00.000
2023-11-01 23:30:00.000
2
2
120

3
2023-11-01 20:30:00.000
2023-11-02 00:00:00.000
2
3
210

3
2023-11-02 00:00:00.000
2023-11-02 01:30:00.000
2
3
90

4
2023-11-02 20:30:00.000
2023-11-02 23:30:00.000
2
1
180

5
2023-11-02 20:30:00.000
2023-11-02 23:30:00.000
2
2
180

6
2023-11-02 20:30:00.000
2023-11-03 00:00:00.000
2
3
210

6
2023-11-03 00:00:00.000
2023-11-03 00:30:00.000
2
3
30


drop table dates

ID	StartDate	EndDate	TaskID	EmployeeID
1	2023-11-01 20:30:00	2023-11-01 23:30:00	2	1
2	2023-11-01 21:30:00	2023-11-01 23:30:00	2	2
3	2023-11-01 20:30:00	2023-11-02 01:30:00	2	3
4	2023-11-02 20:30:00	2023-11-02 23:30:00	2	1
5	2023-11-02 20:30:00	2023-11-02 23:30:00	2	2
6	2023-11-02 20:30:00	2023-11-03 00:30:00	2	3

ID	StartDate	EndDate	TaskID	EmployeeID
1	2023-11-01 20:30:00.000	2023-11-01 23:30:00.000	2	1
2	2023-11-01 21:30:00.000	2023-11-01 23:30:00.000	2	2
3	2023-11-01 20:30:00.000	2023-11-01 00:00:00.000	2	3
3	2023-11-02 00:00:00.000	2023-11-02 01:30:00.000	2	3
4	2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	2	1
5	2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	2	2
6	2023-11-03 00:00:00.000	2023-11-03 00:30:00.000	2	3
6	2023-11-02 20:30:00.000	2023-11-02 00:00:00.000	2	3