SQL数据分摊

/*
----------------------------------------
场景:
到季未公司按照部门贡献率进行对各部门发放奖金,然后部门内部按效绩优先顺序分摊奖金


方案:
参考网上资料,使用CTE函数递归处理
----------------------------------------
*/
if object_id('tempdb..#EmplBonus') is not null drop table #EmplBonus
create table #EmplBonus
(
PN int,--优先序号
DeptNo int,--部门
EmpNo int,--员工
Bonus int--奖金
)
insert into #EmplBonus
values(1,1001,5001,900)
,(2,1001,5002,1000)
,(3,1001,5003,900)
,(4,1002,5004,1500)
,(5,1002,5005,1200)
,(6,1002,5006,1600)
,(7,1003,5007,500)


drop table #TmpBonus
create table #TmpBonus
(
Dept int,--部门
Bonus int--奖金
)
insert into #TmpBonus
values(1001,2000),(1002,6000),(1003,200)


with tmpA as(
SELECT dense_rank() OVER( ORDER BY DeptNo) AS DeptNum,ROW_NUMBER() OVER(PARTITION BY DeptNo ORDER BY DeptNo,PN) AS DeptLn,*
,(select cast(e.Bonus as numeric(24,12))/(case when SUM(tmpB.Bonus)=0 then 1 else SUM(tmpB.Bonus) end) from #EmplBonus tmpB where tmpB.DeptNo=e.DeptNo) Rate
FROM #EmplBonus e
)--部门员工内部序号
,tmpB as(
SELECT (select MAX(DeptLn) from tmpA m where m.DeptNum=tmpA.DeptNum) MaxDeptLn,* FROM tmpA
)--部门内部最大序号
,tmpC (MaxDeptLn,DeptNum,DeptLn,PN,DeptNo,EmpNo,Bonus,CurLeft,Total,PreLeft,Allocate,NoAllocate) AS 
(
SELECT MaxDeptLn,DeptNum,DeptLn,PN,DeptNo,EmpNo,Bonus
,isnull((select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)-Bonus,0) CurLeft --当前剩余分摊数
,Bonus Total
,isnull((select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo),0) PreLeft --前一个剩余分摊数
,isnull(
 (case  when ((select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)-Bonus)<0--业绩奖金大于分摊数则取分摊数
then (select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)
when MaxDeptLn=DeptLn and (select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)>Bonus--最后一个员工业绩奖金小于分摊数则取分摊数
then (select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)
else Bonus end) ,0) Allocate --其他情况取业绩奖金,无部门奖金则为0
 ,case when (select top 1 b.Bonus from #TmpBonus b where b.Dept=tmpB.DeptNo)  is null then 1 else 0 end NoAllocate --是否有部门奖金分摊
FROM tmpB 
WHERE 1=1 and tmpB.DeptLn=1 ----取各部门第1个优先员工开始分摊
UNION ALL
SELECT b.MaxDeptLn,b.DeptNum,b.DeptLn,b.PN,b.DeptNo,b.EmpNo,b.Bonus,a.CurLeft-b.Bonus,a.Total+b.Bonus,a.CurLeft PreLeft
,(case when (a.CurLeft-b.Bonus)<0 then a.CurLeft when b.MaxDeptLn=b.DeptLn and a.CurLeft>b.Bonus then a.CurLeft else b.Bonus end) Allocate,a.NoAllocate
FROM tmpC a,tmpB b
WHERE  1=1
and a.DeptLn=b.DeptLn-1 -----递归对部门下一个员工分摊
AND a.DeptNo=b.DeptNo
AND a.CurLeft>0 --------剩余分摊大于0
)
select a.PN,a.DeptNo,a.EmpNo,a.Bonus,a.CurLeft,a.Total,a.PreLeft
,case when a.NoAllocate=1 then 0 else a.Allocate end Allocate--无部门奖金的重置分摊数为0
from(
SELECT * FROM tmpC
where 1=1
) a
order by a.DeptNo,a.DeptLn