sqlsql-serversql-server-2014

Get total minutes between 2 date time


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

Solution

  • 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