Assuming the above table has attendance details, Name1 has attended classes from 1 to 5, 8 to 20 and 22 till 31st.
The missing dates(absent) are 6,7,21 which i need to capture using a query.
did a search : didn't find it.(this doesn't mean it's not there, just that i can't find properly)
generating a CTE table containing dates (or a sequence of numbers ) is not that hard with recursive SQL
then a simple not exists should give you a result.
With allnumber(nbr) as (
select 1 from sysibm.sysdummy1
select nbr + 1 from allnumbers where nbr < 99)
Select name1 , nbr from allnumbers N, table1 A
where not exists (select * from table1 B where A.name1 = B.name1 and B.nfrom <= N.nbr and B.nto >= N.nbr)
If there are no overlaps , following query (without recursive sql) will give you also a workable result :
select * from
( select name1, 'from' , nto + 1 from table1 t1
where nto < :max_nto
and not exists (select * from table1 t2 where t1.name1 = t2.name1 and t2.nfr = (t1.nto +1))
( select name1, 'to ', nfr - 1 from table1 t1
where nfr > :min_nfr
and not exists (select * from table1 t2 where t1.name1 = t2.name1 and t2.nto = (t1.nfr - 1))
where tos_without_from.name1 = froms_without_to.name1