Re: help on sql
Date: Sat, 10 Oct 2009 08:25:25 +0200
Message-ID: <4ad028cf$0$444$426a34cc_at_news.free.fr>
"RA" <angani_at_gmail.com> a écrit dans le message de news: db579098-d4b0-4778-8f65-3cff8e43d9a2_at_f20g2000prn.googlegroups.com...
| Hi sql gurus,
|
| I need some help and also curious on how to write this in single sql
| statement, requirements goes like this
|
| Table structure
| ================
| create table find_fixed_open (
| find number,
| fixed number,
| open number,
| bug_when date
| );
|
|
| Data
| =============
| insert into find_fixed_open values(5,3,2,'10/01/2009');
| insert into find_fixed_open values(52,38,16,'10/02/2009');
| insert into find_fixed_open values(68,45,39,'10/03/2009');
| insert into find_fixed_open values(112,59,92,'10/04/2009');
| insert into find_fixed_open values(45,12,125,'10/05/2009');
|
|
| Formula
| =========
| Open = Find - fixed + previous rows open(basically open is cumulative)
| i.e.
| 5 - 3 + 0 = 2
| 52 - 38 + 2 = 16
| 68 - 45 + 16 = 39
| 112 - 59 + 39 = 92
| 45 - 12 + 92 = 125
|
| Please help me and enlighten me.
|
| Thank you
| Raju
SQL> select bug_when, find, fixed, open,
2 sum(find-fixed) over (order by bug_when) computed_open
3 from find_fixed_open
4 order by bug_when
5 /
BUG_WHEN FIND FIXED OPEN COMPUTED_OPEN
---------- ---------- ---------- ---------- -------------
10/01/2009 5 3 2 2 10/02/2009 52 38 16 16 10/03/2009 68 45 39 39 10/04/2009 112 59 92 92 10/05/2009 45 12 125 125
5 rows selected.
Regards
Michel
Received on Sat Oct 10 2009 - 01:25:25 CDT