Re: dynamically store a rolling six years of data in a table and have meaningful column field names and descriptions that can change year-to-year?

[CRPence <CRPbottle@xxxxxxxxx>]

On 01-Nov-2011 11:29 , elehti@xxxxxxxxxxxxxxxxxx wrote:
> How can we redesign this application to dynamically store a rolling
> six years of data in a table and have meaningful column field names
> and descriptions that change year-to-year?
>
> We have a custom IBM i table with 12 monthly columns for the year
> 2007, 12 fields for 2008, 12 for 2009, and 36 for 2010, 2011, and
> 2012. Sales history is summed and put into the appropriate month
> buckets. The data is downloaded to a spreadsheet and imported to a
> Windows sales forecasting application which puts Forecast data is put
> into future year buckets.
>
> We upload Future year data to our IBM i Sales Forecast File F3460
> (JD Edwards World).
>
> In January 2012 I will need to delete the 12 fields for 2007 and add
> 12 fields for 2013 and modify the RPG program accordingly.
>
> What redesign would allow us to dynamically store a rolling six
> years of data in a table and have meaningful column field names and
> descriptions that change dynamically from year-to-year?

  Use generically-named columns which describe the relative to current 
year rather than the specific years.  In the examples below I 
incorrectly used minus six to minus zero, but in re-reading I see that 
should be minus five to plus on years... but the concept is the same.

   create table SixYearDta
   ( minus6yr01 /* 6 years ago, Jan */ ...
   , minus6yr02 /* 6 years ago, Feb */ ...
   , ...
   , minus1yr12 /* 1 year  ago, Dec */ ...
   , minus0yr01 /* 0 years ago, Jan */ ...
   , ...
   , minus0yr12 /* 0 years ago, Dec */ ...
   ) ;

  Then instead of ALTER to DROP COLUMN of the old columns and adding 
new columns to replace those, use an UPDATE to SET column data in the 
current year to the NULL value and each prior year to the next year:

   update table SixYearDta
    set minus6yr01 = minus5yr01
      , minus6yr02 = minus5yr02
      , ...
      , minus1yr12 = minus0yr12
      , minus0yr01 = null
      , ...
      , minus0yr12 = null
   ;

> So that I would never need to modify the RPG program nor the file?

  The column headings and text of the fields optionally could be 
changed yearly to reflect the new situation.  Some export features 
enable using either these instead of column names anyhow; and if updated 
could be selected\generated from the catalogs when not.

   LABEL ON COLUMN SixYearDta
   ( minus6yr01 IS '2007-Jan'
   , minus6yr02 IS '2007-Feb'
   , minus1yr12 IS '2011-Jan'
   , minus0yr01 IS '2012-Jan'
   , minus0yr12 IS '2012-Dec'
   ) ;

   LABEL ON COLUMN SixYearDta
   ( minus6yr01 TExT IS '2007-Jan'
   , minus6yr02 TExT IS '2007-Feb'
   , minus1yr12 TExT IS '2011-Jan'
   , minus0yr01 TExT IS '2012-Jan'
   , minus0yr12 TExT IS '2012-Dec'
   ) ;

Regards, Chuck