SQL Server 2022

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

CREATE TABLE YourTableName (
    ID INT,
    StartDateTime DATETIME,
    EndDateTime DATETIME,
    TaskID INT,
    EmployeeID INT
);
INSERT INTO YourTableName (ID, StartDateTime, EndDateTime, TaskID, EmployeeID)
VALUES
    (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);
6 rows affected

WITH RecursiveCTE AS (
    SELECT 
        ID,
        StartDateTime AS StartTime,
        CASE 
            WHEN CAST(StartDateTime AS DATE) = CAST(EndDateTime AS DATE) THEN EndDateTime
            ELSE DATEADD(DAY, 1, CAST(StartDateTime AS DATE))
        END AS NewEndDate,
        EndDateTime AS OriginalEndDateTime,
        TaskID,
        EmployeeID,
        CASE 
            WHEN CAST(StartDateTime AS DATE) = CAST(EndDateTime AS DATE) THEN DATEDIFF(MINUTE, StartDateTime, EndDateTime)
            ELSE DATEDIFF(MINUTE, StartDateTime, DATEADD(DAY, 1, CAST(StartDateTime AS DATE)))
        END AS Minutes,
        EndDateTime AS EndTime,
        StartDateTime AS OriginalStartDate 
    FROM YourTableName
    UNION ALL
    SELECT 
        ID,
        NewEndDate,
        CASE 
            WHEN DATEADD(DAY, 1, CAST(OriginalEndDateTime AS DATE)) < OriginalEndDateTime THEN OriginalEndDateTime
            ELSE DATEADD(DAY, 1, CAST(OriginalEndDateTime AS DATE))
        END AS NewEndDate,
        OriginalEndDateTime,
        TaskID,
        EmployeeID,
        CASE 
            WHEN DATEADD(DAY, 1, CAST(OriginalEndDateTime AS DATE)) < OriginalEndDateTime THEN DATEDIFF(MINUTE, NewEndDate, OriginalEndDateTime)
            ELSE DATEDIFF(MINUTE, NewEndDate, OriginalEndDateTime)
        END AS Minutes,
        EndTime,
        OriginalStartDate 
    FROM RecursiveCTE
    WHERE NewEndDate < OriginalEndDateTime
)
SELECT 
    OriginalStartDate AS [Start],
    OriginalEndDateTime AS [End],
    TaskID,
    EmployeeID AS EmpId,
    Minutes 
FROM RecursiveCTE
ORDER BY  OriginalStartDate,NewEndDate,EmployeeID;


Start
End
TaskID
EmpId
Minutes


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

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

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

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

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

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

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

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

Start	End	TaskID	EmpId	Minutes
2023-11-01 20:30:00.000	2023-11-01 23:30:00.000	2	1	180
2023-11-01 20:30:00.000	2023-11-02 01:30:00.000	2	3	210
2023-11-01 20:30:00.000	2023-11-02 01:30:00.000	2	3	90
2023-11-01 21:30:00.000	2023-11-01 23:30:00.000	2	2	120
2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	2	1	180
2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	2	2	180
2023-11-02 20:30:00.000	2023-11-03 00:30:00.000	2	3	210
2023-11-02 20:30:00.000	2023-11-03 00:30:00.000	2	3	30