How to Identify overlap period from Two tables for multi rows

user_3347048 0
Viewed 107

How do I know which row is overlapping in table1 comparing with the table2?

I have two tables each table has multi rows for date and time columns. I want to know which row in table1 is overlapping comparing with the table2 considering date and time ranges.

Table1

SrNo    DateStart   DateEnd     TimeStart   TimeEnd     IsOverlapping
1       2021-08-01  2021-08-01  01:10:00    01:20:00    NULL
2       2021-08-01  2021-08-01  01:30:00    01:45:00    NULL
3       2021-08-01  2021-08-01  01:31:00    03:00:00    NULL
4       2021-08-02  2021-08-04  01:30:00    01:45:00    NULL
5       2021-08-02  2021-08-04  09:00:00    10:30:00    NULL

Table2

SrNo    DateStart   DateEnd     TimeStart   TimeEnd
1       2021-08-01  2021-08-01  01:00:00    01:15:00
2       2021-08-01  2021-08-01  02:01:00    02:30:00
3       2021-08-01  2021-08-01  03:01:00    03:30:00
4       2021-08-01  2021-08-01  04:01:00    05:10:00
5       2021-08-02  2021-08-04  10:00:00    10:15:00
6       2021-08-02  2021-08-04  11:01:00    11:30:00

EXPECTED RESULT:

SrNo    DateStart   DateEnd     TimeStart   TimeEnd     IsOverlapping
1       2021-08-01  2021-08-01  01:10:00    01:20:00    1
2       2021-08-01  2021-08-01  01:30:00    01:45:00    0
3       2021-08-01  2021-08-01  01:31:00    03:00:00    1
4       2021-08-02  2021-08-04  01:30:00    01:45:00    0
5       2021-08-02  2021-08-04  09:00:00    10:30:00    1

SQL SCRIPT FOR TABLES

DECLARE @DateStart DATE=CONVERT(DATE,'01-Aug-2021'), @DateEnd DATE=CONVERT(DATE,'01-Aug-2021'), @DateStart2 DATE=CONVERT(DATE,'02-Aug-2021'), @DateEnd2 DATE=CONVERT(DATE,'04-Aug-2021')

DROP TABLE IF EXISTS [#Table1];
SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL))[SrNo],  *,(NULL)IsOverlapping INTO [#Table1] FROM (
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'01:10:00')TimeStart, CONVERT(TIME(0),'01:20:00')TimeEnd UNION 
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'01:30:00')TimeStart, CONVERT(TIME(0),'01:45:00')TimeEnd UNION 
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'01:31:00')TimeStart, CONVERT(TIME(0),'03:00:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],@DateEnd2[DateEnd], CONVERT(TIME(0),'01:30:00')TimeStart, CONVERT(TIME(0),'01:45:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],@DateEnd2[DateEnd], CONVERT(TIME(0),'09:00:00')TimeStart, CONVERT(TIME(0),'10:30:00')TimeEnd
)t;SELECT * FROM [#Table1];

DROP TABLE IF EXISTS [#Table2];
SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL))[SrNo],* INTO [#Table2] FROM (
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'01:00:00')TimeStart, CONVERT(TIME(0),'01:15:00')TimeEnd UNION 
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'02:01:00')TimeStart, CONVERT(TIME(0),'02:30:00')TimeEnd UNION 
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'03:01:00')TimeStart, CONVERT(TIME(0),'03:30:00')TimeEnd UNION 
SELECT @DateStart[DateStart],@DateEnd[DateEnd], CONVERT(TIME(0),'04:01:00')TimeStart, CONVERT(TIME(0),'05:10:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],@DateEnd2[DateEnd], CONVERT(TIME(0),'10:00:00')TimeStart, CONVERT(TIME(0),'10:15:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],@DateEnd2[DateEnd], CONVERT(TIME(0),'11:01:00')TimeStart, CONVERT(TIME(0),'11:30:00')TimeEnd 
)t;SELECT * FROM [#Table2];
sql-server-2016 sql-server sql
2 Answers

This article gives an example and explanation of a "numbers function", aka "tally function".

dbo.fnTally

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

query

with
t1_cte as (
    select t1.*, v.calc_dt
    from #Table1 t1
         cross apply dbo.fnTally(0, datediff(day, t1.DateStart, t1.DateEnd)) day_fn
         cross apply dbo.fnTally(0, datediff(minute, t1.TimeStart, t1.TimeEnd)) min_fn
         cross apply (values (convert(datetime, dateadd(day, day_fn.n, t1.DateStart))+
                              convert(datetime, dateadd(minute, min_fn.n, t1.TimeStart)))) v(calc_dt)),
t2_cte as (
    select t2.*, v.calc_dt
    from #Table2 t2
         cross apply dbo.fnTally(0, datediff(day, t2.DateStart, t2.DateEnd)) day_fn
         cross apply dbo.fnTally(0, datediff(minute, t2.TimeStart, t2.TimeEnd)) min_fn
         cross apply (values (convert(datetime, dateadd(day, day_fn.n, t2.DateStart))+
                              convert(datetime, dateadd(minute, min_fn.n, t2.TimeStart)))) v(calc_dt)),
overlap_cte as (
    select tc1.SrNo, count(*) overlap_count
    from t1_cte tc1
         join t2_cte tc2 on tc1.calc_dt=tc2.calc_dt
    group by tc1.SrNo) 
select t1.SrNo, t1.DateStart, t1.DateEnd, t1.TimeStart, t1.TimeEnd,
       case when isnull(oc.overlap_count, 0)>0 then 1 else 0 end IsOverlapping
from #Table1 t1
     left join overlap_cte oc on t1.SrNo=oc.SrNo
order by t1.SrNo;

SrNo    DateStart   DateEnd     TimeStart   TimeEnd     IsOverlapping
1       2021-08-01  2021-08-01  01:10:00    01:20:00    1
2       2021-08-01  2021-08-01  01:30:00    01:45:00    0
3       2021-08-01  2021-08-01  01:31:00    03:00:00    1
4       2021-08-02  2021-08-04  01:30:00    01:45:00    0
5       2021-08-02  2021-08-04  09:00:00    10:30:00    1
user_67487580

If you want any overlap, you can use exists. This would be much simpler with datetime values. Fortunately, you can convert your values to datetimes without too much trouble:

    select t1.*,
       (case when exists (select 1
                          from #table2 t2
                          where convert(datetime, t1.datestart) + convert(datetime, t1.timestart) < convert(datetime, t2.dateend) + convert(datetime, t2.timestart) and
                                convert(datetime, t2.datestart) + convert(datetime, t2.timestart) < convert(datetime, t1.dateend) + convert(datetime, t1.timestart)
                         ) 
            then 1 else 0 end) as IsOverlapping                                 
from #table1 t1;
user_11440350
Related