I am working on selecting participants for a program based on certain criteria and after meeting the criteria, get x% (say 60%) from a specific department A, and the remaining 20% from another department B and the last 20% from department C. I am able to flag rows meeting the criteria in a column named flag_criteria and I do have the department names in the department column, however I don't know how I can get the rows based on the percentage allocation from the department column. As an example:
Now, based on the allocation %, I created a new column "Allocation", and of the 10 rows meeting the criteria, I assigned 60% from Dept A, 20% from B and 20% from C.
How would I handle this in SQL? Also, if there are no rows meeting the allocation %, I would want to give the "extra" allocations to the next dept. Please advise.
By calculating the position in the partition by (dept, flag) and comparing with the % for the dept:
with data(empid, dept, flag) as (
select 12345, 'A', 1 union all
select 23456, 'A', 1 union all
select 34567, 'B', 1 union all
select 45678, 'A', 0 union all
select 56789, 'A', 1 union all
select 67900, 'A', 1 union all
select 79011, 'B', 1 union all
select 90122, 'C', 0 union all
select 101233, 'A', 1 union all
select 112344, 'A', 1 union all
select 123455, 'C', 1 union all
select 134566, 'B', 1 union all
select 145677, 'C', 0 -- union all
),
percentages(dept, pc) as (
select 'A', 60 union all
select 'B', 20 union all
select 'C', 20 -- union all
)
select d.empid, dept, d.flag,
case when flag = 1 and rn <= n*pc/100
then 'y'
else 'n'
end
as allocation
from (
select empid, dept, flag,
row_number() over(partition by dept, flag order by empid) as rn,
count(empid) over(partition by flag) as n
from data
) d
join percentages p using(dept)
order by d.empid
;