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?

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