Re: query help

Perfect Dima, thankyou very much, worked like a dream.

Cheers

Chris

• Original Message ----- From: "Dima" <dima_at_cit.org.by> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 04, 2000 9:55 PM Subject: Re: query help

> Hi,
>
> select * from a;
>
> PAY# DUE_DATE
> --------- ----------
> 345 01.01.1999
> 365 01.03.2000
> 456 02.08.2001
>
>
> select a1.pay#, TRUNC(MONTHS_BETWEEN(a1.due_date, a2.due_date))
> from a a1, a a2
> where a2.pay# = (select nvl(max(pay#), a1.pay#) from a where pay# <
a1.pay#)
> order by a1.pay#;
>
> PAY# TRUNC(MONTHS_BETWEEN(A1.DUE_DATE,A2.DUE_DATE))
> --------- ----------------------------------------------
> 345 0
> 365 14
> 456 17
>
> HTH
> Dima
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, May 04, 2000 2:48 PM
>
>
> > Dear fellow dba's
> >
> > I have a table with the following columns:
> >
> > pay# (integer, p.key)
> > due-date (date)
> >
> > Example records are:
> >
> > 345 1/1/1999
> > 365 1/3/2000
> > 456 2/8/2001
> >
> > I need to write a select that gives me the months between the payments
for
> > each record, ie:
> >
> > 345 0 (no payment before it)
> > 365 14
> > 465 17
> >
> > 14months difference between 365 and 345 for example.
> >
> > Ideas? this is not a test questio btw, i was trying to get out of
writing
> > pl/sql :)
> >
> > Cheers
> >
> > Chris
> >
> >
> >
> > --
> > Author: Chris Kempster
> > INET: ckempste_at_iinet.net.au
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Author: Dima
> INET: dima_at_cit.org.by
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu May 04 2000 - 09:57:58 CDT