如何从多行的两个表中识别重叠期

2024-05-17 • 问答

与 table2 相比，我如何知道 table1 中的哪一行重叠？

我有两个表，每个表都有多行用于日期和时间列。考虑到日期和时间范围，我想知道 table1 中的哪一行与 table2 重叠。

表 1

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

预期结果：

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 脚本

DeclARE @DateStart DATE=CONVERT(DATE,'01-Aug-2021'),@DateEnd DATE=CONVERT(DATE,@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,'01:20:00')TimeEnd UNION 
SELECT @DateStart[DateStart],'01:30:00')TimeStart,'01:45:00')TimeEnd UNION 
SELECT @DateStart[DateStart],'01:31:00')TimeStart,'03:00:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],@DateEnd2[DateEnd],'01:45:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],'09:00:00')TimeStart,'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],'01:00:00')TimeStart,'01:15:00')TimeEnd UNION 
SELECT @DateStart[DateStart],'02:01:00')TimeStart,'02:30:00')TimeEnd UNION 
SELECT @DateStart[DateStart],'03:01:00')TimeStart,'03:30:00')TimeEnd UNION 
SELECT @DateStart[DateStart],'04:01:00')TimeStart,'05:10:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],'10:00:00')TimeStart,'10:15:00')TimeEnd UNION 
SELECT @DateStart2[DateStart],'11:01:00')TimeStart,'11:30:00')TimeEnd 
)t;SELECT * FROM [#Table2];

This article 给出了一个“数字函数”的例子和解释，也就是“计数函数”。

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

查询

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,t2.DateStart,t2.DateEnd)) day_fn
         cross apply dbo.fnTally(0,t2.TimeStart,t2.TimeEnd)) min_fn
         cross apply (values (convert(datetime,t2.DateStart))+
                              convert(datetime,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.DateEnd,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

如果您想要任何重叠，可以使用 exists。使用 datetime 值会简单得多。幸运的是，您可以轻松地将值转换为 datetime：

    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;

如何从多行的两个表中识别重叠期

monkey1210 回答：如何从多行的两个表中识别重叠期

大家都在问