Re: help on sql
Date: Fri, 9 Oct 2009 20:29:17 -0700 (PDT)
Message-ID: <2668891e-369b-4fe1-a9d9-86fbddc63614_at_o41g2000yqb.googlegroups.com>
On Oct 9, 10:39 pm, RA <ang..._at_gmail.com> 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');
> 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
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
Received on Fri Oct 09 2009 - 22:29:17 CDT