Re: Query problem - compare date in first record to date in next record

From: Alien <stijf_at_stijf.com>
Date: 27 Feb 2006 06:05:53 -0800
Message-ID: <1141049153.196590.209640@z34g2000cwc.googlegroups.com>

Hi,

This should do the trick:
SQL> select * from b;

PAY_ID CH DATE_STA DATE_END

----------  --   --------           --------
         1 c1 01-01-00      31-03-00
         2 c2 01-05-01      30-06-02
         3 c1 01-05-00      31-08-00
         4 c3 01-10-00      31-12-00
         5 c2 01-07-02      31-08-02
         6 c4 01-03-03      30-04-03
         7 c4 01-07-03      30-09-03

7 rijen zijn geselecteerd.

SQL> select pid,fp,np from
  2 (select a.child_id cid,a.person_id pid,date_end fp,   3 lead(date_start) over (partition by a.person_id   4 order by b.date_end) np from a,b
  5 where a.child_id=b.child_id)
  6 where fp<np-1;

PI FP             NP
-- --------          --------
p1 31-03-00    01-05-00

p1 31-08-00 01-10-00
p3 30-04-03 01-07-03

SQL> Regards,

Arian Received on Mon Feb 27 2006 - 08:05:53 CST