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
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 |