How to write a SELECT to do ...

From: Ben Ryan <benryan_at_my-deja.com>
Date: Thu, 13 Jan 2000 23:50:24 GMT
Message-ID: <85lobr$q35$1@nnrp1.deja.com>

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.)

TIA, Ben

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 17:50:24 CST