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

From: Logichev, Sergey <Sergey.Logichev_at_ru.artificial-life.com>
Date: Wed, 19 Jan 2000 21:00:27 +0300
Message-ID: <119F66596CB2D311BD3100508B4492ED8DD780@JOHNNY>

You can try this monstrous select (equjoin table with itself):

select t1.ACCOUNT_NO,
       t1.EDATE date1,
       t2.EDATE date2,
       t2.EDATE-t1.EDATE weight

from table t1, table t2
where t1.ACCOUNT_NO = t2.ACCOUNT_NO
and t2.EDATE = (select min(EDATE) from table where ACCOUNT_NO=t2.ACCOUNT_NO

                and EDATE > t1.EDATE)

union
select t1.ACCOUNT_NO,

       t1.EDATE date1,
       to_date('01-jan-2000','dd-mon-yyyy') date2,
       to_date('01-jan-2000','dd-mon-yyyy')-t1.EDATE weight

from table t1
where t1.EDATE = (select max(EDATE) from table where ACCOUNT_NO=t1.ACCOUNT_NO)
order by 1,2,3

Hope, this help you.
Sergey

> -----Original Message-----
> From: Ben Ryan [mailto:benryan_at_my-deja.com]
> Posted At: Friday, January 14, 2000 2:50 AM
> Posted To: server
> Conversation: How to write a SELECT to do ...
> Subject: How to write a SELECT to do ...
>
>
> 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 Wed Jan 19 2000 - 12:00:27 CST