CAMSYSTEMS
asked on
MS SQL - Loop through x number of record columns based on a user parameter
Hi,
I am migrating an old Crystal Report to an alternative Dashboard application.
My crystal report (which works very well, shame I have to migrate) reads from a table of monthly stock sales history.
e.g.
STOCK CODE MONTH1UNITS MONTH2UNITS MONTH3UNITS MONTH4UNITS MONTH6UNITS MONTH7UNITS
ABC123 100 87 69 93 108 111
ETC
ETC
My Crystal Report had a parameter to prompt the user for number of months to report on.
A calculated field in the report registered all 60 of the month columns into an array and then looped through the correct number of months based on the user parameter for number of months to aggregate for each stock record in the report.
This solution was simple, elegant and worked very well.
I don't have the facility of arrays in the alternative Dashboard app.
Can anyone suggest an alternative way of achieving similar through SQL in Stored Procedure? I'm struggling...
Hope i've explained clearly enough?
Thanks in anticipation...
I am migrating an old Crystal Report to an alternative Dashboard application.
My crystal report (which works very well, shame I have to migrate) reads from a table of monthly stock sales history.
e.g.
STOCK CODE MONTH1UNITS MONTH2UNITS MONTH3UNITS MONTH4UNITS MONTH6UNITS MONTH7UNITS
ABC123 100 87 69 93 108 111
ETC
ETC
My Crystal Report had a parameter to prompt the user for number of months to report on.
A calculated field in the report registered all 60 of the month columns into an array and then looped through the correct number of months based on the user parameter for number of months to aggregate for each stock record in the report.
This solution was simple, elegant and worked very well.
I don't have the facility of arrays in the alternative Dashboard app.
Can anyone suggest an alternative way of achieving similar through SQL in Stored Procedure? I'm struggling...
Hope i've explained clearly enough?
Thanks in anticipation...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Take your point re normalisation, thanks.
I've now combined your thoughts with use of UNPIVOT and arrived at a solution that seems to work quite nicely.
Many thanks
Shaun