SQL Matching Time durations (fromDatetime- toDatetime)

Question

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

Answer 1

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