RE: dynamically store a rolling six years of data in a table and havemeaningful column field names and descriptions that can changeyear-to-year?

Interesting. I am curious why you would store YEAR (which obviously is a numeric value, upon which you perform arithmetic) as a character value, and then convert back and forth?
++
Dennis
++
"Too bad all the people who know how to run the country are busy driving taxis and cutting hair."
-- George Burns




Sent from my Galaxy tablet phone. Please excuse my brevity.
For any grammatic/spelling errors, there is no excuse.
++


"Bill Erhardt" <berhardt@xxxxxxxxxxxxxxxx> wrote:

Step 1 - Normalize the data - Each record should contain a year, month,
and sales value.
Step 2 - Create an SQL view like this
Select
Case when year = Char(current Year) then Year else 0 end as
curr_Yr,
Case when year = Char(Current Year - 1) then year else 0 end as
One_Yr_Back,
Case when Year = Char(Current Year - 2) then year else 0 end as
Two_Yr_Back,
Case when year = Char(current Year - 3) then Year else 0 end as
Three_Yr_Back,
Case when year = Char(Current Year - 4) then year else 0 end as
Four_Yr_Back,
Case when Year = Char(Current Year - 5) then year else 0 end as
Five_Yr_Back,
Case when Year = Char(Current Year) and Month = 1 then Sales
else 0 end as Cur_YR_Mon01
Case when Year = Char(Current Year) and Month = 2 then Sales
else 0 end as Cur_Yr_Mon02

Etc....
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
elehti@xxxxxxxxxxxxxxxxxx
Sent: Tuesday, November 01, 2011 2:30 PM
To: midrange-l@xxxxxxxxxxxx
Subject: dynamically store a rolling six years of data in a table and
havemeaningful column field names and descriptions that can
changeyear-to-year?

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? So that I would never need
to modify the RPG program nor the file?

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