Re: RPG/SQL

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Hi Alan

I think he wants both year's total in one record - sort of a pivot kind 
of thing, right?

Vern

On 6/27/2012 12:10 PM, Alan Campin wrote:
> My guess would be
>
> Exec  SQL
>    declare Stor_sls Cursor for
>      Select Year(sdivd),
>                 sdan8,
>                 Sum(sdaexp/100)
>         from f421119
>         where sdivd>=:j_start and
>                  sdivd<=:j_end and
>                  sdlnty not in('T ', 'TN', '% ') and
>                  sdmcu=:mcu
>        group by Year(iDate(sdivd)),
>                      sdan8
>        order by 3 desc;
>
> Only issue here if the sdivd field is true date field. Assuming that
> sdivd is not a true date field but a numeric field you are going to
> need to get into a date field. With iDate it would be something this.
>
> Exec  SQL
>    declare Stor_sls Cursor for
>      Select Year(iDate(sdivd)),
>                 sdan8,
>                 Sum(sdaexp/100)
>         from f421119
>         where sdivd>=:j_start and
>                  sdivd<=:j_end and
>                  sdlnty not in('T ', 'TN', '% ') and
>                  sdmcu=:mcu
>        group by Year(iDate(sdivd)),
>                      sdan8
>        order by 3 desc;
>
>
> On Wed, Jun 27, 2012 at 11:00 AM, Graves, Chuck <cgraves@xxxxxxxxxxxxxx> wrote:
> > What type of select statement do I need to produce a data set containing:
> >
> > Customer#
> > 2011 sales
> > 2012 sales
> >
> > Generated from a file containing sales from several years.
> >
> > I am using this statement:
> >
> > exec SQL
> >     declare stor_sls cursor for
> >           select sdan8,
> >                  sum(sdaexp/100)
> >           from f4211l9
> >           where sdivd>=:j_start and sdivd<=:j_end and
> >                 sdlnty not in('T ', 'TN', '% ') and
> >                 sdmcu=:mcu group by sdan8
> >                    order by sum(sdaexp) desc;
> >
> > This statement produces what I want for the given date range..i.e.
> >
> > Customer#
> > 2012 sales
> >
> >
> > but I need a separate field for the prior years sales
> >
> > Do I declare a separate cursor for the prior year and then join the two???
> >
> > Thanks in advance...
> > Chuck
> >
> >
> >
> >
> >
> >
> > [Rodda Paint Turns 80!] Chuck Graves
> >   Director of Information Systems
> >   Rodda Paint Co.<http://www.roddapaint.com>
> >   6107 N. Marine Drive
> >   Portland, Oregon 97203
> >   (503) 737-6042
> >
> > --
> > This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.