2

I need to find Gaps between DateRanges of base and test ranges using sql.here is my example SD and ED are start and End Dates. all rows for both A and B are in same table.

A's Date

ID SD ED A 20130101 20130531 A 20130601 20131031

B's Date

 ID SD ED B 20130120 20130420 B 20130601 20130830 B 20130910 20131130 
Output should be: the Dates that are in A but are not in B with no dates overlaps

Missing Gap Ranges

SD ED 20130101 20130119 20130421 20130531 20130831 20130909

i looked at some example in here http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:529176000346581356 but they did not scenario like mine.

Improve this question

1 Answer 1

Reset to default
3 
select to_char(SD, 'yyyymmdd') as SD, to_char(ED, 'yyyymmdd') as ED from ( -- prepare gaps in each A after removing all B select BED + 1 as SD, lead(BSD, 1, AED + 1) over (partition by ASD,AED order by BSD) - 1 as ED from ( -- prepare all intersections between A and B select AA.sd as ASD, AA.ed as AED, BB.sd as BSD, BB.ed as BED from AA join BB on least(AA.ed, BB.ed) >= greatest(AA.sd, BB.sd) union all select AA.sd, AA.ed, to_date('1000','yyyy'), AA.sd - 1 from AA ) ) where SD <= ED -- exclude empty gaps order by 1

fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

this is great. Thanks a Lot!!!!. my data is integer and not date in Db and in a single table , can you add comments on this as to what is doing what in here its pretty cool sql but not sure how its working
@NatashaThapa - To convert number to date: to_date(number, 'yyyymmdd'), to convert date to number: to_number(to_char(date, 'yyyymmdd'))
could you please add some comments as to what each section of sql is doing if i have to change
how to handle if you have an overlap in A to begin with and you want to avoid that overlap in B

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.