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
| with tab1(Id, days_relap) as
(select 1, 030 union all
select 2, 040 union all
select 3, 365 union all
select 4, 012 union all
select 3, 180 union all
select 4, 090 union all
select 1, 040 union all
select 1, 400 union all
select 5, 020 union all
select 4, 390 union all
select 5, 003 union all
select 5, 020
)
, tab2(Id, Nb, N183, N365) as
(select Id
, count(*)
, sum(case when days_relap < 184 then 1 else 0 end)
, sum(case when days_relap < 366 then 1 else 0 end)
from tab1
group by Id
order by Id
)
select Id
, Nb
, case when N183 > 1 then 'FRRL'
when N365 > 2 then 'FRRL'
else 'ESP'
end as status
from tab2 |
Partager