CODEWITHSUNDEEP
sqlsql-servert-sql
Nikhil
Nikhil

Reputation: 51

Combine the list of intervals from two tables

Need MS SQL Query to generate the result as shown below:

TABLE 1 Contains list of hourly start dates & end dates. Another table, TABLE 2 contains list of downtime start dates & end dates. Result required is list of all the dates as shown in RESULT table.

TABLE 1 

  StartTime                EndTime
2018-08-27 08:00:00  2018-08-27 09:00:00  
2018-08-27 09:00:00  2018-08-27 10:00:00  
2018-08-27 10:00:00  2018-08-27 11:00:00  
2018-08-27 11:00:00  2018-08-27 12:00:00  
2018-08-27 12:00:00  2018-08-27 13:00:00

TABLE 2 

  StartTime               EndTime
2018-08-27 08:25:00  2018-08-27 08:30:00
2018-08-27 10:20:00  2018-08-27 10:30:00

RESULT 

   StartTime                EndTime
2018-08-27 08:00:00  2018-08-27 08:25:00
2018-08-27 08:25:00  2018-08-27 08:30:00
2018-08-27 08:30:00  2018-08-27 09:00:00
2018-08-27 09:00:00  2018-08-27 10:00:00
2018-08-27 10:00:00  2018-08-27 10:20:00 --<< see changes
2018-08-27 10:20:00  2018-08-27 10:30:00 --<< period 10:00-11:00 got split
2018-08-27 10:30:00  2018-08-27 11:00:00 --<< as per 10:20-10:30 period from table2
2018-08-27 11:00:00  2018-08-27 12:00:00
2018-08-27 12:00:00  2018-08-27 13:00:00

Upvotes: 1

Views: 142

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

I'd suggest that you union all start and endtimes and then build upon intervals using INNER JOIN or LEAD (in newer versions of SQL server) See live demo

; with inputs  as
( select t=starttime from table1
 union 
 select t=endtime from table1
 union 
 select t=starttime from table2
 union
 select t=endtime from table2
 ),
  map as 
 (
     select starttime=t, endtime=lead(t) over ( order by t) 
     from inputs
  )

 select * from map 
 where endtime is not null
 order by starttime

Upvotes: 1

Related Questions