What about using embedded SQL in composition with OLAP Ranking Functions:
Select Dense_Rank() Over(Order By Revenue) RankRevenue,
Dense_Rank() Over(Order By Miles) RankMiles,
Dense_Rank() Over(Order BY RPM) RankRPM,
... additional fields
From ...
Where ...
Order By Fldx , Fldy
The fields in the final Order by may be different from the fields specified
in the Over(Order By) clauses.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Scott Mildenberger
Gesendet: Friday, 20.7 2012 19:46
An: rpg400-l@xxxxxxxxxxxx
Betreff: Displaying rankings in several categories simultaneously
We have trucks that we want to rank in different categories and display
these multiple rankings at the same time. For example, the categories are
Revenue, Miles, Revenue per Mile, etc. Revenue per Mile(RPM) is calculated
based on Revenue and Miles. We plan on keeping these numbers with a record
for each month for viewing history. We plan on being able to display a
units history (several months at a time) and show their rankings within each
month. For example Truck 1234 in Jan 2012 was ranked 7th in revenue, 4th in
miles, and 12th in RPM - In Feb it was ranked 2nd, 9th, and 1st
respectively.
Revenue
Miles RPM
Jan 2012 7 4
12
Feb 2012 2 9
1
The current approach is that this history file has fields in it for the
actual data as well as each ranking - every time the file is updated then
the rankings are determined for each category by reading the file in a
different sequence and updating the ranking field.
My question - Anyone have a better approach as to how to get these rankings
perhaps on the fly when the data is displayed. I was thinking about using
an array(s) of data structures and just resorting for each ranking for each
month. This approach should work but it does mean reading the data from all
the trucks for each month displayed to do the ranking. I use SQL but aren't
real experienced with it so I don't know if there is a way for it to do the
ranking within a month but only return the data for the unit to be
displayed.
Maybe the original method of updating and storing the ranking in each record
of the file is best but it always bothers me to store calculated values in a
file.
Thanks,
Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
