Re: query help

Hi Chris,

try this :

select
  a.pay#
, months_between(a.due_date, nvl(b.due_date, a.due_date)) mnths from
  table1 a
, table1 b
where a.pay# >= b.pay#
and 1 >= (

    select count(*)
    from table1 c
    where c.pay# < a.pay#
    and c.pay# >= b.pay#
)
and ((a.pay# <> b.pay#)
  or (a.pay# = (
    select min(pay#)
    from table1 c
    )))

HTH, Chris Kempster wrote:

> 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).
Received on Thu May 04 2000 - 09:43:43 CDT