query issue:return date just greater than a particular date
947561Jul 19 2012 — edited Jul 19 2012I have a date column p_date in DP_TOTAL d, I want
1) corresponding date from another column p_date in AR_TOTAL a such that a.p_date is just greater than d.p_date (i.e greater than and minimum of all other days)
2) difference in days between the two dates.
join condition is: a.P_NO_S=d.P_NO_S
For eg I ran below query which returned a number of rows and I am just showing a part of it:
SQL> select distinct a.p_date, d.p_date, d.P_NO_S from AR_TOTAL a, DP_TOTAL d where a.P_NO_S=d.P_NO_S and a.p_date>=d.p_date and d.P_NO_S='Z1575560';
29/07/2011 20/02/2011 Z1575560
*06/03/2011 20/02/2011 Z1575560*
03/04/2011 20/02/2011 Z1575560
29/05/2011 20/02/2011 Z1575560
08/05/2011 20/02/2011 Z1575560
*29/05/2011 20/05/2011 Z1575560*
29/07/2011 20/05/2011 Z1575560
I want the output as the rows in bold and want to discard the other rows (06/03/2011 >= 20/02/2011 and 06/03/2011 = min (29/07/2011,06/03/2011,03/04/2011,29/05/2011,08/05/2011))
Desired output:
06/03/2011 20/02/2011 Z1575560 14 days
29/05/2011 20/05/2011 Z1575560 9 days
Please assist.