sql - TSQL - Query Distincts and aggregated Overlaps

Question

Sample Data:

DECLARE @ExpectedBookingTimes TABLE
    (
        [EId]         INT
      , [BookingDate] DATE
      , [BeginDT]     DATETIME2(7)
      , [EndDT]       DATETIME2(7)
      , [BookingType] INT
    )
DECLARE @RealBookingTimes TABLE
    (
        [RId]         INT
      , [BookingDate] DATE
      , [BeginDT]     DATETIME2(7)
      , [EndDT]       DATETIME2(7)
      , [BookingType] INT
    ) ;

INSERT INTO @ExpectedBookingTimes
VALUES
    ( 1, '2021-01-01', '2021-01-01 12:00:00.0000000', '2021-01-01 12:59:59.9999999', 1 )
  , ( 2, '2021-01-02', '2021-01-02 12:00:00.0000000', '2021-01-02 12:59:59.9999999', 2 )
  , ( 3, '2021-01-03', '2021-01-03 12:00:00.0000000', '2021-01-03 13:59:59.9999999', 1 ) ;

INSERT INTO @RealBookingTimes
VALUES
    ( 10, '2021-01-01', '2021-01-01 13:00:00.0000000', '2021-01-01 13:59:59.9999999', 2 )
  , ( 20, '2021-01-02', '2021-01-02 12:15:00.0000000', '2021-01-02 12:44:59.9999999', 2 )
  , ( 30, '2021-01-03', '2021-01-03 13:00:00.0000000', '2021-01-03 14:59:59.9999999', 2 )
  , ( 40, '2021-01-03', '2021-01-03 15:00:00.0000000', '2021-01-03 15:59:59.9999999', 2 ) ;

Goal:

-- Within same BookingDate

---- If Expected BookingType = Real BookingType, only return records from @RealBookingTimes

-----> Between EId = 2 and RId = 20, exclude EId = 2 and return RId = 20 record

---- Else if there is datetime overlap, return MIN(BeginDT) and MAX(EndDT)

-----> Between EId = 3 and RId = 30, return BeginDT = 2021-01-03 12:00:00.0000000 and EndDT = 2021-01-03 14:59:59.9999999

---- Else, return everything else

-----> RId = 1, EId = 10, and EId = 40 ;

Expected Output:

EId,    RId,    BookingDate     BeginDT,                        EndDT
1,      NULL,   '2021-01-01',   '2021-01-01 12:00:00.0000000',  '2021-01-01 12:59:59.9999999' (Met condition # 3)
NULL,   10,     '2021-01-01',   '2021-01-01 13:00:00.0000000',  '2021-01-01 13:59:59.9999999' (Met condition # 3)
NULL,   20,     '2021-01-02',   '2021-01-02 12:15:00.0000000',  '2021-01-02 12:44:59.9999999' (Met condition # 1)
3,      30      '2021-01-03',   '2021-01-03 12:00:00.0000000',  '2021-01-03 14:59:59.9999999' (Met condition # 2)
NULL,   40,     '2021-01-03',   '2021-01-03 15:00:00.0000000',  '2021-01-03 15:59:59.9999999' (Met condition # 3)

What I'm thinking:

To meet condition # 1, I can do an INNER JOIN on BookingDate AND BookingType and delete matched from @ExpectedBookingTimes

To meet condition # 2, I can do an INNER JOIN on BookingDate AND overlap check and return Min/Max (not so sure about this one yet)

Everything else would fall into condition # 3 bucket.

I would then UNION ALL them together, ordered by [BeginDT] ASC.

But this seems like an expensive process since the real tables will be quite large. So basically trying to figure out an efficient way to query this data

Answer 1

等待大神解答