Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!193.201.147.86.MISMATCH!news.astraweb.com!border5.a.newsrouter.astraweb.com!newsfeed.eweka.nl!eweka.nl!feeder3.eweka.nl!de-l.enfer-du-nord.net!usenet-fr.net!proxad.net!feeder1-2.proxad.net!cleanfeed2-a.proxad.net!nnrp4-1.free.fr!not-for-mail From: "Michel Cadot" Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc References: Subject: Re: help on sql Date: Sat, 10 Oct 2009 08:25:25 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5843 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 Lines: 61 Message-ID: <4ad028cf$0$444$426a34cc@news.free.fr> Organization: Guest of ProXad - France NNTP-Posting-Date: 10 Oct 2009 08:25:20 MEST NNTP-Posting-Host: 82.67.171.166 X-Trace: 1255155920 news-4.free.fr 444 82.67.171.166:2291 X-Complaints-To: abuse@proxad.net Xref: news.cambrium.nl comp.databases.oracle.server:100959 "RA" a écrit dans le message de news: db579098-d4b0-4778-8f65-3cff8e43d9a2@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