SQL Matching Time durations (fromDatetime- toDatetime)-CodePudding

I have 2 tables

Table A

...	StartTime	EndTime
...	2021.01.01 7:15:00	2021.01.01 9:30:00

Table B

...	StartTime	EndTime
...	2021.01.01 6:10:00	2021.01.01 6:30:00
...	2021.01.01 7:00:00	2021.01.01 7:32:00
...	2021.01.01 7:45:00	2021.01.01 9:15:00

Starting from table A, I would like to have the data split according to the time in table B. Where the times in table B would be the correct times intervals to use.

An example of what I mean The result should be:

...	StartTime	EndTime
...	2021.01.01 7:15:00 (from A)	2021.01.01 7:32:00 (from B)
...	2021.01.01 7:45:00 (from B)	2021.01.01 9:15:00 (from B)

create table #A (
    StartTime datetime
    , EndTime datetime
);

create table #B (
    StartTime datetime
    , EndTime datetime
);

insert into #A
select {ts'2021-01-01 07:15:00'}, {ts'2021-01-01 09:30:00'}

insert into #B
select {ts'2021-01-01 06:10:00'}, {ts'2021-01-01 06:30:00'}
insert into #B
select {ts'2021-01-01 07:00:00'}, {ts'2021-01-01 07:32:00'}
insert into #B
select {ts'2021-01-01 07:45:00'}, {ts'2021-01-01 09:15:00'}


Drop Table #A, #B

CodePudding user response：

First you join B with A where the two ranges overlap (i.e. some part of one range overlaps some part of the other range). Once you found the overlapping rows, clamp the start and end dates of B, if necessary:

SELECT
    CASE WHEN a.StartTime > b.StartTime THEN a.StartTime ELSE b.StartTime END AS s,
    CASE WHEN a.EndTime   < b.EndTime   THEN a.EndTime   ELSE b.EndTime   END AS e
FROM b
JOIN a ON a.EndTime >= b.StartTime AND b.EndTime >= a.StartTime

DB<>Fiddle

CodePudding user response：

I'd inner join on A including B and then use a row_number for ordering:

select 
     A.StartTime as OriginalStartTime
    ,A.EndTime as OriginalStartTime
    ,row_number() over (order by B.StartTime) as portion
    ,B.StartTime as PortionStartTime
    ,B.EndTime as PortionEndTime
from
    TableA as as
    inner join TableB as B on B.StartTime>=A.StartTime and B.EndTime<=A.EndTime
order by 3 asc