Get total minutes between 2 date time

Question

I need to get a total minutes between 2 date time with the requirement where the time fall to another date should not count the minutes for the StartDate. Eg data as below

  ID        Start                End            taskId            EmployeeId
   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

Start and End Column data type are dateTime, the rest 2 are int.

The data row 3, the total minute count from 20:30 to 00:00, then the 1 and the half hour fall on 2023-11-2, the minutes should count to the date for 2023-11-2. It is possible to do so?

Select 
    Case
    When 
        (Cast(EndDate as date) = Cast(EndDate as date) and Cast(StartDate as date) != Cast(EndDate as date)) then DateDiff(minute, Convert(datetime, Convert(varchar, Cast(EndDate as date)) + ' 00:00:00'), enddate)
    When
        DateDiff(Day, StartDate, EndDate) = 1 then DateDiff(Minute, StartDate, Convert(datetime, Convert(varchar, DateAdd(day, 1, Cast(StartDate as date))) + ' 00:00:00')) 
    else 
        DateDiff(MINUTE, StartDate, EndDate) End as [Total 
    Minutes], EmployeeId, taskId
From
    tbl
Group By
  EmployeeId, taskId

Expected result

   Start                End           taskId   EmpId       minutes
2023-11-1 20:30:00  2023-11-1 23:30        2         1       180
2023-11-1 21:30:00  2023-11-1 23:30        2         2       120
2023-11-1 20:30:00  2023-11-2 01:30        2         3       210
2023-11-1 20:30:00  2023-11-2 01:30        2         3        90
2023-11-2 20:30:00  2023-11-2 23:30        2         1       180
2023-11-2 20:30:00  2023-11-2 23:30        2         2       180
2023-11-2 20:30:00  2023-11-3 00:30        2         3       210
2023-11-2 20:30:00  2023-11-3 00:30        2         3        30

Answer 1

Another approach could be to gather the rows seperate if they pass midnight

I get all rows that start and end on the same day
then all rows that pass midnight with the enddate on midnight
then all rows from midnight until enddate

now I use that collection to calculate the differences

I made an example in this dbFiddle

it looks something like this

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

and the result is

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

Answer 2

You can use Recursive CTE. It will be a little bit complicated. Here is the sample code:

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;

Here is sample output:

You can check on dbfiddle here

Answer 3

Here's an alternative solution:

select id
, cast(start as datetime) as startdate
, cast([end] as datetime) as enddate
, taskid, employeeid
into #data
from (
    VALUES  (1, N'2023-11-1 20:30:00', N'2023-11-1 23:30', 2, 1)
    ,   (2, N'2023-11-1 21:30:00', N'2023-11-1 23:30', 2, 2)
    ,   (3, N'2023-11-1 20:30:00', N'2023-11-2 01:30', 2, 3)
    ,   (4, N'2023-11-2 20:30:00', N'2023-11-2 23:30', 2, 1)
    ,   (5, N'2023-11-2 20:30:00', N'2023-11-2 23:30', 2, 2)
    ,   (6, N'2023-11-2 20:30:00', N'2023-11-3 00:30', 2, 3)
) t (ID, Start, [End], taskId, EmployeeId)


select id, employeeid, a.*
from #data d
cross apply (
        select case when datediff(day, startdate, enddate) > 0 then cast(Enddate as date) else enddate end as edgedate
    ) e
cross apply (
        select startdate
        ,   edgedate
        ,   datediff(minute, startdate, edgedate) as mins
        union all
        select edgedate
        ,   enddate
        ,   datediff(minute, edgedate, enddate) as mins
        where edgedate <> enddate
    ) a

First, we create a pseudo column called edgeDate, which is either Enddate or CAST(Enddate as date) to handle if employee time overflows to next day.

Then to create a new row, a UNION ALL is used which generates one row between StartDate and EdgeDate, and a potential second row in case of overflow.

This code also fixes up enddates so they look more correct (in my opinion at least):

Output:

id	employeeid	startdate	edgedate	mins
1	1	2023-11-01 20:30:00.000	2023-11-01 23:30:00.000	180
2	2	2023-11-01 21:30:00.000	2023-11-01 23:30:00.000	120
3	3	2023-11-01 20:30:00.000	2023-11-02	210
3	3	2023-11-02	2023-11-02 01:30:00.000	90
4	1	2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	180
5	2	2023-11-02 20:30:00.000	2023-11-02 23:30:00.000	180
6	3	2023-11-02 20:30:00.000	2023-11-03	210
6	3	2023-11-03	2023-11-03 00:30:00.000	30

Answer 4

This recursive Common Table Expression (CTE) is effective under normal circumstances. However, in cases where overlapping occurs, it may not yield the desired results. In such instances, it's advisable to explore the GAP and ISLAND query approach for a more suitable solution.

-- Create the table
CREATE TABLE TimeTracking (
    ID INT PRIMARY KEY,
    StartDateTime DATETIME,
    EndDateTime DATETIME,
    TaskID INT,
    EmployeeID INT
);
-- Insert data into the table
INSERT INTO TimeTracking (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);

-- Query to calculate minutes and generate the required output
WITH TimeCTE AS (
    SELECT
            'Original' Orig, ID, StartDateTime, EndDateTime, TaskID, EmployeeID, 
    CASE
        WHEN DATEDIFF(dd, StartDateTime, EndDateTime) >= 1 and DATEDIFF(dd, StartDateTime, EndDateTime) < 2
            THEN DATEADD(DAY, 1, CONVERT(varchar(8), StartDateTime, 112))
        ELSE EndDateTime
    END AS PrevEndDateTime
    FROM
        TimeTracking 
    UNION ALL
    SELECT
            'Added   ' Orig, T.ID, T.StartDateTime, PrevEndDateTime EndDateTime, 
            T.TaskID, T.EmployeeID, NULL PrevEndDateTime
    FROM
        TimeTracking T JOIn TimeCTE C on T.ID = C.ID and T.EmployeeID = C.EmployeeID
    WHERE T.EndDateTime <> C.PrevEndDateTime
), CTE_2 AS (
    SELECT ID, Orig,
        CASE WHEN PrevEndDateTime > StartDateTime and PrevEndDateTime < EndDateTime 
        Then DATEADD(ss,1, PrevEndDateTime) Else StartDateTime END AS StartDateTime,
        EndDateTime, TaskID, EmployeeID
    FROM TimeCTE 
    ) Select 
            StartDateTime, EndDateTime, Taskid, EmployeeID, 
            DATEDIFF(mi, StartDateTime, EndDateTime)  mins
        from CTE_2
        Order by ID, Taskid, StartDateTime, Orig