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 42 43 44 45 46 47 48
| with T1 (t1sem, t1ca) as
(select 202301, 100 union all
select 202302, 110 union all
select 202303, 040 union all
select 202304, 080 union all
select 202305, 125 union all
select 202306, 090 union all
select 202307, 100 union all
select 202308, 130 union all
select 202309, 070 union all
select 202310, 065 union all
select 202311, 100 union all
select 202312, 110
)
, TX (xsem, xtot, xcum3, xcum5, xcum7) as
(select t1sem
, sum(t1ca) over()
, sum(t1ca) over(order by t1sem
rows between 1 preceding and 1 following)
, sum(t1ca) over(order by t1sem
rows between 2 preceding and 2 following)
, sum(t1ca) over(order by t1sem
rows between 3 preceding and 3 following)
from T1
)
, TY (ysem, ypct3, yecar3, ypct5, yecar5, ypct7, yecar7) as
(select xsem
, (xcum3 * 100) / xtot
, abs(50-(xcum3 * 100) / xtot)
, (xcum5 * 100) / xtot
, abs(50-(xcum5 * 100) / xtot)
, (xcum5 * 100) / xtot
, abs(50-(xcum7 * 100) / xtot)
from TX
)
select *
from (select ysem
, least(yecar3, yecar5, yecar7) as EC0
, case when least (yecar3, yecar5, yecar7) = yecar3 then '3 sem'
when least (yecar3, yecar5, yecar7) = yecar5 then '5 sem'
else '7 sem'
end as duree
from TY main
) Z
order by Z.EC0 asc
offset 0 rows
fetch next 1 rows only |
Partager