1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| With sr1 as
(
select r1.famille,
r1.cattc,
r1.cattc + ratio_to_report(r1.cattc) over() * r2.cattc as new_cattc1,
round(r1.cattc + r2.cattc * ratio_to_report(r1.cattc) over()) as new_cattc2,
r1.cattc + r2.cattc * ratio_to_report(r1.cattc) over() - round(r1.cattc + r2.cattc * ratio_to_report(r1.cattc) over()) as ecart_arr,
r2.cattc as cattc_e,
r1.nb,
r1.nb + round(r2.nb * ratio_to_report(r1.nb) over(), 5) as new_nb
from repartition r1
cross join repartition r2
where r1.famille <> 'E'
and r2.famille = 'E'
)
, sr2 as
(
select famille, cattc, new_cattc1, new_cattc2, ecart_arr, cattc_e, nb, new_nb,
sum(new_cattc2) over() as calc,
sum(cattc) over() + cattc_e as expd,
row_number() over(order by ecart_arr asc, famille asc) as rna,
row_number() over(order by ecart_arr desc, famille asc) as rnd
from sr1
)
select famille, cattc,
case
when calc - expd > 0 and rna = 1
then new_cattc2 - calc + expd
when calc - expd < 0 and rnd = 1
then new_cattc2 - calc + expd
else new_cattc2
end as new_cattc,
nb, new_nb
from sr2
order by famille asc;
FAMILLE CATTC NEW_CATTC NB NEW_NB
A 1553 1548 274516.23 273046.77572
B 5062 5047 191774.1 190747.55497
C 2205 2198 30895.29 30729.91101
D 4129 4117 539744.6 536855.4083 |
Partager