A) What logic are you applying to determine the the output is a singe row with those specific values?
B) Why would you care what method was used to solve the problem so long as the problem is solved in an efficient manner?
Logic:
Suppose column Start and End are minutes.
Jack is a student. table A and table B have data of study time of Jack for subject Math and Physic.
Jack Studied Math from minutes 1 to 4, again 7 to 10 , so on (Table a)
Jack Studied Physic from minutes 1 to 5, again 7 to 10 , so on (Table a)
I want to find out the range Jack studied only Physic, i.e. 4 to 5
SELECT
be.[START],
be.[END],
TOTAL = be.[END] - be.[START]
FROM
#A a
JOIN #B b
ON a.START = b.START
CROSS APPLY ( VALUES (
CASE WHEN a.[END] < b.[END] THEN a.[END] ELSE b.[END] END,
CASE WHEN a.[END] > b.[END] THEN a.[END] ELSE b.[END] END
) ) be ([START], [END])
WHERE
a.[END] <> b.[END];
Select
C.[Start],
C.[End],
C.[End] - C.[Start] As [Total]
From #A A
join #B B on A.[START] BETWEEN B.[START] And B.[END]
Or B.[START] BETWEEN A.[START] And A.[END]
Cross Apply
(
Select
Case When A.[Start] > B.[Start] Then A.[Start] Else B.[Start] End As [Start],
Case When A.[END] < B.[END] Then A.[END] Else B.[END] End As [END]
) C