SQL Query to find missing numbers between 2 values

Question

Need your expertise in writing an SQL for the below scenario

I have a single row in a table "range_num" as follows.

start_num end_num

10 14

Is there any way we can write a query to find all the gaps between 10 and 14 i ..e 11,12,13 in the below format

col1 col2 col3 col4 col5 10 11 12 13 14

I am using Teradata databases , so any compliant SQL query will be of great help!!

No , it will vary in one row it is 10 and 14 , in another row it can be 3 to 7 ETC .. Please help! — SRG
Say if you have range like 1-15 then you want to have 15 columns as well?? — anwaar_hell
yes .. range can be from 1-15 or 0-13 or 3-11 , these are the possible scenarios and there will be multiple rows of them .. — SRG

anwaar_hell anwaar_hell · Accepted Answer · 2015-10-20T09:59:14

Here is solution for your problem...

    select 
max(case when   rownum=1 then gap end) as col1,max(case when   rownum=2 then gap end) as col2,
max(case when   rownum=3 then gap end) as col3,max(case when   rownum=4 then gap end) as col4,
max(case when   rownum=5 then gap end) as col5,max(case when   rownum=6 then gap end) as col6,
max(case when   rownum=7 then gap end) as col7,max(case when   rownum=8 then gap end) as col8,
max(case when   rownum=9 then gap end) as col9,max(case when   rownum=10 then gap end) as col10,
max(case when   rownum=11 then gap end) as col11,max(case when   rownum=12 then gap end) as col12,
max(case when   rownum=13 then gap end) as col13,max(case when   rownum=14 then gap end) as col14,
max(case when   rownum=15 then gap end) as col15,max(case when   rownum=16 then gap end) as col16,
max(case when   rownum=17 then gap end) as col17,max(case when   rownum=18 then gap end) as col18,
max(case when   rownum=19 then gap end) as col19,max(case when   rownum=20 then gap end) as col20,
max(case when   rownum=21 then gap end) as col21,max(case when   rownum=22 then gap end) as col22,
max(case when   rownum=23 then gap end) as col23
from
(
select a.id_start,
b.day_of_calendar as gap,
csum(1,1) as rownum
from db_sok.testt  a,
sys_calendar.calendar b
where b.day_of_calendar between a.id_start and a.id_end
)X

do a little tweaking in case you need something else...:)

SQL Query to find missing numbers between 2 values

1 Answers