create table x_sandbox_syntetika.ext_63984482_az_02_filtr_hvlp as
    select
        distinct pseudonym_nzis
    from x_nrhzs_data.v_polozka_01_hvlp
    where diagnoza like 'C33%' or diagnoza like 'C34%' or diagnoza like 'C384%'
;
create table x_sandbox_syntetika.ext_63984482_az_02_filtr_vykon as
    select
        distinct pseudonym_nzis
    from x_nrhzs_data.v_polozka_00_zdravotni_vykon
    where diagnoza like 'C33%' or diagnoza like 'C34%' or diagnoza like 'C384%'
;
create table x_sandbox_syntetika.ext_63984482_az_02_filtr as
    select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_02_filtr_hvlp
    union
    select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_02_filtr_vykon
;
create table x_sandbox_syntetika.ext_63984482_az_02_hvlp as
    select
        pseudonym_nzis,
        TO_CHAR(den, 'YYYYMM') as rok_mesic,
        cis_atc_kod as pripravek_vykon,
        mnozstvi as mnozstvi_pocet,
        case
            when kod_polozky = '0250354' then mnozstvi * 1.00
            when kod_polozky = '0232991' then mnozstvi * 0.24
            when kod_polozky = '0232990' then mnozstvi * 1.00
            when kod_polozky = '0209153' then mnozstvi * 1.00
            when kod_polozky = '0209152' then mnozstvi * 0.49
        end as davka,
        case
            when extract(year from den) - rok_narozeni between 0 and 5 then '0-5 let'
            when extract(year from den) - rok_narozeni between 6 and 10 then '6-10 let'
            when extract(year from den) - rok_narozeni between 11 and 15 then '11-15 let'
            when extract(year from den) - rok_narozeni between 16 and 20 then '16-20 let'
            when extract(year from den) - rok_narozeni between 21 and 25 then '21-25 let'
            when extract(year from den) - rok_narozeni between 26 and 30 then '26-30 let'
            when extract(year from den) - rok_narozeni between 31 and 35 then '31-35 let'
            when extract(year from den) - rok_narozeni between 36 and 40 then '36-40 let'
            when extract(year from den) - rok_narozeni between 41 and 45 then '41-45 let'
            when extract(year from den) - rok_narozeni between 46 and 50 then '46-50 let'
            when extract(year from den) - rok_narozeni between 51 and 55 then '51-55 let'
            when extract(year from den) - rok_narozeni between 56 and 60 then '56-60 let'
            when extract(year from den) - rok_narozeni between 61 and 65 then '61-65 let'
            when extract(year from den) - rok_narozeni between 66 and 70 then '66-70 let'
            when extract(year from den) - rok_narozeni between 71 and 75 then '71-75 let'
            when extract(year from den) - rok_narozeni between 76 and 80 then '76-80 let'
            when extract(year from den) - rok_narozeni between 81 and 85 then '81-85 let'
            when extract(year from den) - rok_narozeni between 86 and 90 then '86-90 let'
            when extract(year from den) - rok_narozeni between 91 and 95 then '91-95 let'
            else 'Starsi 95 let'
        end as vekovy_interval,
        icp as icp
    from x_nrhzs_data.v_polozka_01_hvlp
    where
        pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_02_filtr)
        and cis_atc_kod in (
            'L01BC05', 'L01XA01', 'L01XA02', 'L01CA01', 'L01CA04', 'L01DB01', 'L01CD01', 'L01FF01', 'L01FF05', 'L01FF02',
            'L01BA04', 'L01EB04', 'L01CB01', 'L01FF03', 'L01CD01', 'L01FG01', 'L01FX04', 'L01CD02', 'L01EX09', 'L01FG02',
            'L01EB03', 'L01FX18', 'L01EB02', 'L01EB01', 'L01EB07', 'L01ED03', 'L02ED04', 'L01ED02', 'L01ED01', 'L01ED05',
            'L01EX14', 'L01EX12', 'L01EC02', 'L01EE01', 'L01XX73', 'L01EX23', 'L01EX22', 'L01EX21', 'L01EX17', 'L01CE02',
            'L01CE01', 'L01CA02', 'L01AA01', 'L01AA06', 'L01FD04', 'L01FF06'
        )
;
create table x_sandbox_syntetika.ext_63984482_az_02_vykon as
    select
        pseudonym_nzis,
        TO_CHAR(den, 'YYYYMM') as rok_mesic,
        kod_polozky as pripravek_vykon,
        mnozstvi as mnozstvi_pocet,
        null::integer as davka,
        case
            when extract(year from den) - rok_narozeni between 0 and 5 then '0-5 let'
            when extract(year from den) - rok_narozeni between 6 and 10 then '6-10 let'
            when extract(year from den) - rok_narozeni between 11 and 15 then '11-15 let'
            when extract(year from den) - rok_narozeni between 16 and 20 then '16-20 let'
            when extract(year from den) - rok_narozeni between 21 and 25 then '21-25 let'
            when extract(year from den) - rok_narozeni between 26 and 30 then '26-30 let'
            when extract(year from den) - rok_narozeni between 31 and 35 then '31-35 let'
            when extract(year from den) - rok_narozeni between 36 and 40 then '36-40 let'
            when extract(year from den) - rok_narozeni between 41 and 45 then '41-45 let'
            when extract(year from den) - rok_narozeni between 46 and 50 then '46-50 let'
            when extract(year from den) - rok_narozeni between 51 and 55 then '51-55 let'
            when extract(year from den) - rok_narozeni between 56 and 60 then '56-60 let'
            when extract(year from den) - rok_narozeni between 61 and 65 then '61-65 let'
            when extract(year from den) - rok_narozeni between 66 and 70 then '66-70 let'
            when extract(year from den) - rok_narozeni between 71 and 75 then '71-75 let'
            when extract(year from den) - rok_narozeni between 76 and 80 then '76-80 let'
            when extract(year from den) - rok_narozeni between 81 and 85 then '81-85 let'
            when extract(year from den) - rok_narozeni between 86 and 90 then '86-90 let'
            when extract(year from den) - rok_narozeni between 91 and 95 then '91-95 let'
            else 'Starsi 95 let'
        end as vekovy_interval,
        icp as icp
    from x_nrhzs_data.v_polozka_00_zdravotni_vykon
    where
        pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_02_filtr)
        and kod_polozky in (
            '15460', '25507', '25136', '25021', '25022', '25023', '25024', '25110', '25111', '25113', '25114', '25117',
            '25118', '25119', '25121', '25124', '25125', '25126', '25127', '25128', '25129', '25139', '25143', '25151',
            '25152', '25098', '25099', '43210', '43213', '43215', '43217', '43219', '43311', '43313', '43315', '43317',
            '43319', '43434', '43601', '43603', '43605', '43611', '43631', '43633', '43635', '43637', '43638', '43641',
            '42520', '42510', '42021', '42022', '42023', '57111', '57117', '57235', '51881', '57247', '57249', '57251',
            '57245', '57269', '87213', '87231', '87618', '87700', '87701', '87211', '87618', '89611', '89613', '89615',
            '89617', '89619', '89663', '89664', '89665', '47355', '89131'
        )
;
select
    dense_rank() over (order by res.pseudonym_nzis) as pacient_id,
    res.rok_mesic,
    res.pripravek_vykon,
    sum(res.mnozstvi_pocet) as mnozstvi_pocet,
    sum(res.davka) as davka,
    res.vekovy_interval,
    TO_CHAR(um.DatumUmrti, 'YYYYMM') as rok_mesic_umrti,
    mkn_cis.mkn_kod3 as pricina_umrti_mkn3,
    icp_cis.icz,
    nor.poradi as morfologie_poradi,
    nor.morfologie_zdroj,
    nor.morfologie_kod,
    nor.morfologie_grade,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    TO_CHAR(nor.diagnoza_datum, 'YYYYMM') AS rok_mesic_diagnozy
from (
    select pseudonym_nzis, rok_mesic, pripravek_vykon, mnozstvi_pocet, davka, vekovy_interval, icp
    from x_sandbox_syntetika.ext_63984482_az_02_hvlp
    union
    select pseudonym_nzis, rok_mesic, pripravek_vykon, mnozstvi_pocet, davka, vekovy_interval, icp
    from x_sandbox_syntetika.ext_63984482_az_02_vykon
) res
left join x_nrhzs_data.v_umrti um on um.pseudonym_nzis = res.pseudonym_nzis
left join x_nrhzs_ciselniky.V_CIS_MKN mkn_cis on um.PrvotniPricinaSmrtiMKN10 = mkn_cis.mkn_kod5
left join x_nrhzs_ciselniky.v_cis_icp icp_cis on res.icp = icp_cis.icp
left join (
    select
        rank() OVER (PARTITION BY nor.pseudonym_nzis order by nor.diagnoza_datum) as poradi,
        nor.pseudonym_nzis,
        nor.morfologie_zdroj,
        nor.morfologie_kod,
        nor.morfologie_grade,
        nor.T_vysl,
        nor.N_vysl,
        nor.M_vysl,
        nor.diagnoza_datum
   from x_nzis_data.v_nor nor
   where (nor.diagnoza like 'C33%' or nor.diagnoza like 'C34%' or nor.diagnoza like 'C384%')
) nor on nor.pseudonym_nzis = res.pseudonym_nzis
where res.pseudonym_nzis in (select pseudonym_nzis from x_sandbox_syntetika.ext_63984482_az_02_filtr)
group by
    res.pseudonym_nzis,
    res.rok_mesic,
    res.pripravek_vykon,
    res.vekovy_interval,
    icp_cis.icz,
    TO_CHAR(um.DatumUmrti, 'YYYYMM'),
    mkn_cis.mkn_kod3,
    nor.poradi,
    nor.morfologie_zdroj,
    nor.morfologie_kod,
    nor.morfologie_grade,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    TO_CHAR(nor.diagnoza_datum, 'YYYYMM')
order by dense_rank() over (order by res.pseudonym_nzis), rok_mesic;


