Re: SQL and a Running/Moving Total

Yeah, didn't read the OP close enough..

My original would have 1990, 1991, 1993 --> 1993
He wanted 1990,1991,1993 --> 1990

select a.name, a.year, a.amt + b.amt + c.amt as TotalAmt
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)

Charles

On Thu, Oct 8, 2009 at 4:12 PM, Mark Murphy/STAR BASE Consulting Inc.
<mmurphy@xxxxxxxxxxxxxxx> wrote:

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.

--
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.


--
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.