Re: How to write a SELECT to do ...

From: Remco Blaakmeer <remco_at_rd1936.quicknet.nl>
Date: 14 Jan 2000 00:57:40 GMT
Message-ID: <85lsa4$sut$1@rd1936.quicknet.nl>

In article <85lobr$q35$1_at_nnrp1.deja.com>,

        Ben Ryan <benryan_at_my-deja.com> writes:
> Given that I have table containing the following.
> (Where Account_no, edate is the prime key).
>
> ACCOUNT_NO EDATE CLOSING_BALANCE
> ---------- --------- -----------------------
> 26 01-DEC-99 19505.37
> 26 07-DEC-99 19531.69
> 26 13-DEC-99 21702.33
> 26 29-DEC-99 20702.33
> 26 30-DEC-99 19702.33
> 26 31-DEC-99 21182.88
>
> Does anyone know how to write a SELECT statement
> to produce the following result.
>
> ACCOUNT_NO DATE CLOSING_BALANCE WEIGHT
> ---------- --------- ----------------------- ------
> 26 01-DEC-99 19505.37 6
> 26 07-DEC-99 19531.69 6
> 26 13-DEC-99 21702.33 16
> 26 29-DEC-99 20702.33 1
> 26 30-DEC-99 19702.33 1
> 26 31-DEC-99 21182.88 1
>
> Where weight is the number days to the date specified
> in the next row.
> e.g. for the first row it would be
> TO_DATE('07-DEC-99') - TO_DATE('01-DEC-99')
>
> (The last row would have to be compared with 1-JAN.)

This could be done by outer-joining the table to itself:

select  a.account
,       a.edate
,       a.closing_balance
,       nvl(b.edate,add_months(trunc(a.edate,'YY'),12)) - a.edate weight
from    balance_table a
,       balance_table b
where   b.account (+) = a.account
and     b.edate (+) > a.edate
and     (       b.edate = (     select  min(edate)
                                from    balance_table
                                where   account = a.account
                                and     edate > a.edate)
        or      b.edate is null)

order by 1,2
/

In the first two lines of the WHERE condition, table B is outer-joined to table A. The second line is made superfluous by the lines that follow, but it is still needed to make the join work.

If nothing is found in B, this means the record found in A is actually the last record. In this case, B.EDATE is NULL. Then, add_months(trunc(a.edate,'YY'),12) takes the edate in the record from A, truncates it to a whole year (resulting in 01-JAN-99) and then adds 12 months to that. This is a trick to get January, 1st of the next year.

Remco
--
rd1936: 1:45am up 5 days, 6:24, 8 users, load average: 2.67, 2.74, 2.33 Received on Thu Jan 13 2000 - 18:57:40 CST