# 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 