Finding time start date and end date overlaps in two tables-CodePudding

Using SQL Server 2016, I have two tables, both include datetime columns for an event start and event end.

My objective is to see event times in from table 1 where there are time gaps in table 2. Table 1 will be sequential, meaning no time overlaps, but table 2 will have overlaps due to different users being in at different times.

In my example table1.eventId = 3 has a big gap in table2. I'd like way to find it. I have looked at using a cross join, then some case statements in the select with sub queries, perhaps a recursive CTE?

Source data example

Table 1

 eventId| startDateTime       |   endDateTime
---------------------------------------------------
1       | 10-29-2021 00:01:00 | 10-29-2021 00:20:00
2       | 10-29-2021 00:20:00 | 10-29-2021 00:31:00
3       | 10-29-2021 00:31:00 | 10-29-2021 01:04:00
4       | 10-29-2021 01:04:00 | 10-29-2021 03:15:00
5       | 10-29-2021 03:15:00 | 10-29-2021 04:02:00

Table 2

 eventId| user  |startUserDateTime   |   endUserDateTime
------------------------------------------------------
1       | user1 |10-29-2021 00:01:00 | 10-29-2021 00:31:05
1       | user2 |10-29-2021 00:01:15 | 10-29-2021 00:31:10
3       | user1 |10-29-2021 01:04:00 | 10-29-2021 03:18:00
4       | user2 |10-29-2021 01:04:00 | 10-29-2021 04:02:02

Edit: After consideration I would like the eventual output to be something like this, each table1.eventId would have user total user minutes from table2 for that segment. userTotalMinutes would be the sum of all user minutes fall into the time range of each table1.eventId

eventId| startDateTime       |   endDateTime | userTimeTotalMinutes | 
    -----------------------------------------------------------
    1       | 10-29-2021 00:01:00 | 10-29-2021 00:20:00 | 40
    2       | 10-29-2021 00:20:00 | 10-29-2021 00:31:00 | 22
    3       | 10-29-2021 00:31:00 | 10-29-2021 01:04:00 | 0
    4       | 10-29-2021 01:04:00 | 10-29-2021 03:15:00 | 142
    5       | 10-29-2021 03:15:00 | 10-29-2021 04:02:00 | 47

CodePudding user response：

I'm not confident this is the best way, but I think it's pretty intuitive and you could build off of it:

SELECT t1.*, 
 (SELECT sum(v) 
 FROM 
  (SELECT datediff(minute, CASE WHEN t2.startdatetime > t1.startdatetime THEN t2.startdatetime ELSE t1.startdatetime END, CASE WHEN t2.enddatetime > t1.enddatetime THEN t1.enddatetime ELSE t2.enddatetime END) v 
  FROM table2 t2) a 
 WHERE v > 0)
FROM table1 t1

For each row in table1, it sums the relevant ranges in table2. The relevant ranges are determined by calculating the difference between the greater startdatetime and the lesser enddatetime (and then excluding negative values which have no overlap).

CodePudding user response：

Few things:

The way you calculated times are not clear e.g If table1.eventId =1 occurred between 00:01:00 and 00:20:00 (20 minute duration) for now I considered this as 19 minutes
For eventId 4 both user1 and user2 are available all time, please check your desired output in question once.
Below is good start to achieve what you want, modify as per needs.
Please someone help me to format query with indentations, I'm not able to present the way I want and below is what I can do best with this editor.(Sorry for this)

`SELECT a.eventId,a.startDateTime,a.endDateTime,

SUM(
    CASE 
        WHEN b.endUserDateTime <= a.startDateTime THEN 0
        WHEN b.startUserDateTime <= a.startDateTime and b.endUserDateTime <= a.endDateTime THEN DATEDIFF(mi,a.startDateTime,b.endUserDateTime)
        WHEN b.startUserDateTime <= a.startDateTime and b.endUserDateTime > a.endDateTime THEN DATEDIFF(mi,a.startDateTime,a.endDateTime)
        WHEN b.startUserDateTime > a.endDateTime THEN 0
        WHEN b.startUserDateTime > a.startDateTime and b.endUserDateTime <= a.endDateTime THEN DATEDIFF(mi,b.startUserDateTime,b.endUserDateTime)
        WHEN b.startUserDateTime > a.startDateTime and b.endUserDateTime > a.endDateTime THEN DATEDIFF(mi,b.startUserDateTime,a.endDateTime)
    END )

FROM Table1 a INNER JOIN Table2 b ON 1=1 GROUP BY a.eventId,a.startDateTime,a.endDateTime`