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 ----- From: "Chris Kempster" <ckempste_at_iinet.net.au> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, May 04, 2000 2:48 PM Subject: query help

> 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 - 07:47:25 CDT