CODEWITHSUNDEEP
sqlsql-servert-sql
doug_72
doug_72

Reputation: 11

Finding time start date and end date overlaps in two tables

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

Upvotes: 1

Views: 403

Answers (3)

ADITYA PAWAR
ADITYA PAWAR

Reputation: 695

Few things:

  1. 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
  2. For eventId 4 both user1 and user2 are available all time, please check your desired output in question once.
  3. Below is good start to achieve what you want, modify as per needs.
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

Upvotes: 0

dogyog
dogyog

Reputation: 340

If you don't like the subquery in the SELECT, you can use a CTE to accomplish the same goal in a different manner.

DECLARE @t1 TABLE (eventID INT, startDateTime DATETIME, endDateTime DATETIME)
INSERT INTO @t1
VALUES
(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')

DECLARE @t2 TABLE (eventID INT, userID VARCHAR(10), startDateTime DATETIME, endDateTime DATETIME)
INSERT INTO @t2
VALUES
(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');

WITH CTE_Alias
AS
(
    SELECT t2.eventID,
        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) AS v 
  FROM @t2 t2
    INNER JOIN @t1 t1 ON t2.eventID = t1.eventID
)

SELECT t.eventID,
    t.startDateTime,
    t.endDateTime,
    ISNULL(SUM(v),0) AS userTimeTotalMinutes
FROM @t1 t
    LEFT JOIN CTE_Alias c ON t.eventID = c.eventID
GROUP BY t.eventID, t.startDateTime, t.endDateTime
eventID startDateTime endDateTime userTimeTotalMinutes
1 2021-10-29 00:01:00.000 2021-10-29 00:20:00.000 38
2 2021-10-29 00:20:00.000 2021-10-29 00:31:00.000 0
3 2021-10-29 00:31:00.000 2021-10-29 01:04:00.000 0
4 2021-10-29 01:04:00.000 2021-10-29 03:15:00.000 131
5 2021-10-29 03:15:00.000 2021-10-29 04:02:00.000 0

Upvotes: 0

EdmCoff
EdmCoff

Reputation: 3576

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

Upvotes: 2

Related Questions