Question
variable sum across columns
I've been trying to figure out how to automate this, and
can't seem to work
it out. I've hodge-podged it before, requiring me to update the "current
month" every month, and I'm sure there's got to be a better (automatic) way
of doing this. I'm hoping someone can provide the missing pieces.
In an SQL table I have one record for each unique ID that provides last
year's monthly sales figures, and the current year's monthly sales figures
as they become available. It is possible for any of the sales value fields
(previous or current) to be zero (0):
[salesdata]
ID PrevJan PrevFeb PrevMar ... PrevDec CurrJan CurrFeb
CurrMar ... CurrDec
Each month I am given an updated version of this table which will include
the most recent "Curr" month's values.
This table underlies an asp (VBScript) page which reports this data. A
table on the page lists the monthly values (prev & curr), the percentage of
change monthly, and the year-to-date totals of all three (prev, curr,
percent of change).
JAN FEB MAR APR MAY JUN ...
DEC YTD
PREV 5 5 5 5 5
5 5 20
CURR 10 10 10 0 0
0 0 30
% CHANGE 200 200 200 200
150
** The year-to-date, of course, being the sums of all months UP TO THE MOST
CURRENT DATA uploaded into the table. **
The above example shows what I need to do. Note that:
1) The most current month's data is APR (not May as it seems). This ID had
no sales in CURR April.
2) The PREV YTD value sums only Jan-Apr, as does CURR YTD.
Here are my stumbling blocks:
1) What is the most efficient way to sum the COLUMNs across the row to gain
my YTD values, and still only grab the ones relevant for my current YTD? In
SQL? On my page?
2) How can I automate the YTD calculations of PREV to include ONLY the
"current" months (up to the currently imported data) ... allowing for the
fact that the latest month may have ZERO sales?
Again, I've hodge-podged this in the past by manually creating all the YTD
possibilities in my asp page, pulling a "lastest data" value from the
database, and using it in a CASE call to find which summing formulas to use
... but now that some other changes are forcing me to revisit this, I'm
looking for a more "correct" solution.
Thanks for your suggestions.
it out. I've hodge-podged it before, requiring me to update the "current
month" every month, and I'm sure there's got to be a better (automatic) way
of doing this. I'm hoping someone can provide the missing pieces.
In an SQL table I have one record for each unique ID that provides last
year's monthly sales figures, and the current year's monthly sales figures
as they become available. It is possible for any of the sales value fields
(previous or current) to be zero (0):
[salesdata]
ID PrevJan PrevFeb PrevMar ... PrevDec CurrJan CurrFeb
CurrMar ... CurrDec
Each month I am given an updated version of this table which will include
the most recent "Curr" month's values.
This table underlies an asp (VBScript) page which reports this data. A
table on the page lists the monthly values (prev & curr), the percentage of
change monthly, and the year-to-date totals of all three (prev, curr,
percent of change).
JAN FEB MAR APR MAY JUN ...
DEC YTD
PREV 5 5 5 5 5
5 5 20
CURR 10 10 10 0 0
0 0 30
% CHANGE 200 200 200 200
150
** The year-to-date, of course, being the sums of all months UP TO THE MOST
CURRENT DATA uploaded into the table. **
The above example shows what I need to do. Note that:
1) The most current month's data is APR (not May as it seems). This ID had
no sales in CURR April.
2) The PREV YTD value sums only Jan-Apr, as does CURR YTD.
Here are my stumbling blocks:
1) What is the most efficient way to sum the COLUMNs across the row to gain
my YTD values, and still only grab the ones relevant for my current YTD? In
SQL? On my page?
2) How can I automate the YTD calculations of PREV to include ONLY the
"current" months (up to the currently imported data) ... allowing for the
fact that the latest month may have ZERO sales?
Again, I've hodge-podged this in the past by manually creating all the YTD
possibilities in my asp page, pulling a "lastest data" value from the
database, and using it in a CASE call to find which summing formulas to use
... but now that some other changes are forcing me to revisit this, I'm
looking for a more "correct" solution.
Thanks for your suggestions.
