Find total minutes without the overlapping times

user_10457655 0
Viewed 135

I was trying to find total number of minutes without the overlapping minutes. Below is sample table:

CREATE TABLE tim (starttime DATETIME, endtime DATETIME) 
INSERT INTO tim 
VALUES 
('2022-01-3 12:03:00.000',  '2022-01-3 12:20:00.000'),  
('2022-01-3 12:01:00.000',  '2022-01-3 12:06:00.000'),  
('2022-01-3 03:01:00.000',  '2022-01-3 03:06:00.000'),  
('2022-01-3 01:00:00.000',  '2022-01-3 01:26:00.000'),
('2022-01-3 01:03:00.000',  '2022-01-3 01:28:00.000'), 
('2022-01-3 01:05:00.000',  '2022-01-3 01:30:07.000')

When I run this line of query to select the time differences in the table, I get 17, 5, 5, 30 and 25 for the 5 consecutive rows (output is on the table below).

SELECT starttime, endtime, DATEDIFF(MINUTE,starttime, endtime) Diff FROM tim
starttime endtime diff
2022-01-3 12:03:00.000 2022-01-3 12:20:00.000 17
2022-01-3 12:01:00.000 2022-01-3 12:06:00.000 5
2022-01-3 03:01:00.000 2022-01-3 03:06:00.000 5
2022-01-3 01:00:00.000 2022-01-3 01:30:00.000 30
2022-01-3 01:03:00.000 2022-01-3 01:28:00.000 25
2022-01-3 01:05:00.000 2022-01-3 01:30:07.000 25

However, since the first two rows (row 1 and 2) and the last two rows (row 4 and 5) are overlapping, I would like to have the below records instead: Expected output:

starttime endtime diff
2022-01-3 01:00:00.000 2022-01-3 01:30:07.000 30
2022-01-3 03:01:00.000 2022-01-3 03:06:00.000 5
2022-01-3 12:01:00.000 2022-01-3 12:20:00.000 19

I was trying to apply LAG(starttime) over (order by starttime) and LEAD (starttime) over (order by starttime) , but they couldn't get it done for me consistently.

Any guide is appreciated.

tsql sql-server sql
2 Answers

I think I have a lag/lead implementation that gives your desired results, does the following work for you?

with t as (
  select starttime,
    case when Lag(endtime) over (order by starttime) > starttime then 0 else 1 end UseStart,
    case when endtime < Lead(starttime) over(order by starttime) 
        then endtime 
      else Lead(endtime,1,endtime) over (order by starttime) 
    end endtime
 from tim
)
select starttime, endtime, DateDiff(minute,starttime, endtime) Diff 
from t
where UseStart=1

Working fiddle

user_153326500

First, select every starttime you want. Use joins or subqueries to find if the starttime appears between the starttime and endtime in a different row or not. If it does, filter the current row out.

Second, find the best endtime for each starttime. Use a subquery to find all rows where the current row's starttime is between that starttime and endtime. Then select the latest (greatest) endtime.

user_9377600
Related