Finding out the total overlapping time range

SQL Server Administration
1

There are two tables. One consists data of tickets open against machine1 that is SSTID=1 and the other table consists data of the same tickets closed against the same machine.
I want to find the total overlapping time period. against machine1 that is SSTID=1 . although in both the table the column name createddatetime is same,the one in the openticket table is the createdatetime of the ticket and the latter table closeticket- createdatetime is actually the closingdatetime of the ticket.!

sqlquestion
sqlquestion848×326 129 KB

2

here's some working DDL. What are you expecting for results??

Create table #Open (OpenID int, SSTID int, CreatedDateDatetime datetime)

Create table #Close (CloseID int, OpenID int, SSTID int, CreatedDateDatetime datetime)


insert into #Open values 
(1,1,'4/27/2020 13:30:32.337'),
(2,1,'4/27/2020 13:30:32.337'),
(3,1,'4/27/2020 13:30:32.337')


insert into #Close values
(1,1,1,'4/27/2020 18:40:32.337'),
(2,2,1,'4/27/2020 18:40:32.337'),
(3,3,1,'4/27/2020 15:40:32.337')
3

I want the total overlapping time against that machine(sstid=1) by comparing all the datetime range against each other. so i want two columns in the output-SSTID,OVERLAPPINGTIME.