Re: SQL and a Running/Moving Total

["Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>]

Shouldn't that be:

select a.name, a.year, sum(a.amt + b.amt + c.amt)
from myfile a
         inner join myfile b on a.name = b.name and b.year = (a.year + 1)
         inner join myfile c on a.name = c.name and c.year = (a.year + 2)
group by a.name, a.year

did you test it?  I had a hard time wrapping my head around what would 
happen the other way.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx



From:
Charles Wilt <charles.wilt@xxxxxxxxx>
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
10/08/2009 04:01 PM
Subject:
Re: SQL and a Running/Moving Total
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



select a.name, a.year, sum(a.amt + b.amt + c.amt)
from myfile a
         inner join myfile b on a.name = b.name and a.year = (b.year + 1)
         inner join myfile c on a.name = c.name and a.year = (c.year + 2)
group by a.name, a.year

Performance is not going to be great.
RPG records level access or an SQL cursor may be a better solution.

HTH,
Charles

On Thu, Oct 8, 2009 at 3:40 PM, Mark Allen <scprideandms@xxxxxxxxx> wrote:
> Table has 3 columns,Name,year,count  and I need a total of the "count" 
field
> for each name over a 3 yr period, if there are NOT 3 years available 
then no
> output row.
>
> Example:
> Bill  1990    72
> Bill  1991    73
> Bill  1992    71
> Bill  1993    55
> Bill  1994    78
> Bill  1995    73
> Stan  1990    130
> Stan  1991    122
> Stan  1992    100
> Stan  1993    115
> Stan  1994    101
> Stan  1995    112
>
> What I want from the SQL output  is
>
> Bill  1990    216
> Bill  1991    199
> Bill  1992    204
> Bill  1993    206
> Stan  1990    352
> Stan  1991    337
> Stan  1992    316
> Stan  1993    328
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.