Issue
I wish to choose only 3 OBS per branch, where I need to answer these rules:
- if there are only 2 accounts in the branch - bring the 2 highest incomes from the first branch and 1 from the second account
- if there are 3 accounts in the branch - bring 1 observation per account, which are the highest ones.
- if there are 4 or more accounts per branch - bring 1 observation per account, , which are the highest ones - whilst not repeating the same account twice
Solution
Hmmm . . . The row_id
column seems to be ordering the incomes within an account. So, you should be able to use proc sql
, although it is a little messy:
select t.*
from t join
(select crm_branch_id, count(distinct account_id) as cnt
from t
group by crm_branch_id
) b
on b.crm_branch_id = t.crm_branch_id
where (cnt = 1 and t.row_id <= 3) or
(cnt = 2 and t.row_id = 1 or
cnt = 2 and t.row_id = 2 and
t.income = (select max(t2.income)
from t t2
where t2.crm_branch_id = t.crm_branch_id and
t2.row_id = 2
)
) or
(cnt = 3 and row_id = 1) or
(cnt > 3 and row_id = 1 and
(select count(*)
from t t2
where t2.crm_branch_id = t.crm_branch_id and
t2.row_id = 1 and
t2.income >= t.income
) <= 3
);
The arcane logic in the where
clause is to handle the different numbers of accounts:
- If there is one account, take the top three rows.
- If there are two accounts, take the two rows with
row_id = 1
and then the top row whererow_id = 2
. - If there are three accounts, take the rows with
row_id = 1
. - If there are four or more accounts, only consider rows where
row_id = 1
. Then take the top three of those based on income.
Answered By - Gordon Linoff Answer Checked By - Mildred Charles (PHPFixing Admin)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.