×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Birgitta, Rob, Charles & Vern:
Thanks for your replies. With your help we should be able to do this in
one statement. I've never seen Ceiling used before and had to look it up.
Cross Join threw me because I never thought of using it in this manner (or
any other). I still need to read more on using the Rank() Over function
as I'm not fully grasping it yet.
Thanks again.
Jack
Hi,
Why not simply using several Common Table Expressions:
1. X - Determines the Number of Records/Rows/Ranks to be selected (Ceiling
rounds up to the next full integer, i.e. 1.25 = 2 but 1 = 1)
2. Y - Determines the Rank of the B0Balance
3. z - Joins x and y to determine the Average of the 25% top balances
The final select statement joins y and z to get the average of the 25% into
each row.
With x as (Select Ceiling(Count(*) * .25) NbrOfRows
From CVACTDTL
Where B0Balance > 0),
y as (Select Rank() Over(Order By B0Balance Desc) MyRank, B0Balance
From CVACTDTL),
Z as (Select Avg(B0Balance) MyAvg
From y cross join x
Where MyRank <= NbrofRows)
Select y.*, MyAvg
From y cross join z;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
midrange.com
