Re: SQL and a Running/Moving Total

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.