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];
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