help on sql

[RA]

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

[Ed Prochak]

So is this the result you want:
2
16
39
92
125
??????

or a single summand, as in
294
??????????

(Hint to other posters: clear descriptions of your problem go a long
way to getting help)

>
> Please help me and enlighten me.
>
> Thank you
> Raju

Aside from OPEN being a keyword,

Break the problem down into steps.
The individual counts can be obtained using
SELECT (find-fixed+open) opencount from find_fixed_open;

Assuming you wanted the individual counts, you can stop here.

That basically gives you a table of the individual counts. Now given
that table, how would you compute the sum? Here's a hint:
SELECT SUM(opencount) ...

Assuming you want the single summation, can you finish the work by
putting these hints together into one statement?

HTH,
Ed

[Michel Cadot]

"RA" <ang...@gmail.com> a �crit dans le message de news: db579098-d4b0-4778...@f20g2000prn.googlegroups.com...

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

[Raju Angani]

Sorry for the messup

Sample Data

=============
insert into find_fixed_open values(5,3,2,'10/01/2009');

insert into find_fixed_open values(52,38,14,'10/02/2009');
insert into find_fixed_open values(68,45,23,'10/03/2009');
insert into find_fixed_open values(112,59,53,'10/04/2009');
insert into find_fixed_open values(45,12,33,'10/05/2009');

Output should look like this
===================
'10/01/2009',5,3,2
'10/02/2009',52,38,16
'10/03/2009',68,45,39
'10/04/2009',112,59,92
'10/05/2009',45,12,125

thanks
Raju

[Raju Angani]

Michael,

You were spot on, thanks for the input.

Regards
Raju

[Raju Angani]

I'm very sorryyyyy Michel, I got your name wrong.

[Frank van Bortel]

RA wrote:
> 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');

>-------------------------------------------^^^^^^^^^^

Aaaargghhhh! That NOT a date - it's a string! I read it
as ten divided by one, divided by twothousandandnine

Promise to never, ever do that again! Typecast your
data! You should have use the to_date function here!
--

Regards,
Frank van Bortel