|
View previous topic :: View next topic
|
| Author |
Message |
lanand_hps
New User
Joined: 05 Dec 2007
Posts: 82
Location: chennai
|
|
|
|
Hi,
My requirement is to find out the missing dates in table like
Name Start-date End-date
Name1 1 5
Name1 8 20
Name1 22 31
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.
Please advise.
Thanks,
Anand |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
ask one of your classmates.
use the forum search; this question was asked by the last class. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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.
| Code: |
With allnumber(nbr) as (
select 1 from sysibm.sysdummy1
union all
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 :
| Code: |
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))
) tos_without_from
union all
( 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))
) froms_without_to
where tos_without_from.name1 = froms_without_to.name1 |
|
|
| Back to top |
|
|
|
|
