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

From: UserX <stienes_at_advalvas.be>
Date: 27 Feb 2006 03:19:54 -0800
Message-ID: <1141039194.127428.113440@t39g2000cwt.googlegroups.com>

Hi,

I need a query who can compare the date from one record to the date from the next record. Can someone help me with this?

I better explain it with a example :

I have 2 tables :

Table A
child_id person_id
c1 p1
c2 p2
c3 p1
c4 p3
c5 p1

Table B
pay_id child_id date_start date_end

1 c1 01/01/2000 31/03/2000
2 c2 01/05/2001 30/06/2002
3 c1 01/05/2000 31/08/2000
4 c3 01/10/2000 31/12/2000
5 c2 01/07/2002 31/08/2002
6 c4 01/03/2003 30/04/2003
7 c4 01/07/2003 30/09/2003

Now I need all those records who have a interruption between end_date period 1 and start_date period 2 grouped by person_id

result :
person_id end_first_period start_next_period

p1 31/03/2000 01/05/2000
p1 31/08/2000 01/10/2000
p3 30/04/2003 01/07/2003

I had a query something like this :

select
a.person_id,

min(b.date_end) as start_period,
max(b.date_end) as end_period,
from table_a a, table_b b

where a.child_id = b.child_id
group by a.child_id

But then I have max and min_date for all the records in the group by and that's not correct.

I hope I explained my need and find someone who can help me with this. Tnkx Received on Mon Feb 27 2006 - 05:19:54 CST