Missing Range from SQl Table - TSQL

I have two Tables

CREATE TABLE #A ([START] INTEGER, [END] INTEGER, [TOTAL] INTEGER)
CREATE TABLE #B ( [START] INTEGER, [END] INTEGER, [TOTAL] INTEGER)
INSERT INTO #A VALUES (1, 4, 3),(7,10,3), (12,15,3), (15,17,2)
INSERT INTO #B VALUES (1, 5, 4),(7,10,3), (12,17,4)

I Want to fetch the missing range from these tables , Start - 4 End - 5
OUTPUT would be 4,5,1

Can we solve this without using any cte or temp table or functions ?

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?

1 Like

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

Something like the following?

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

Suggested script is not giving exact output.

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

Should do it I think

Hi

Please explain CLEARLY so that we can understand the data

Then we can work on solution

Its confusing ...

Thanks

if you need to learn how to explain
please let me know
:slight_smile: