CREATE TABLE x_sandbox_syntetika.ext_00098892_fnol_01_filtr_hvlp AS
SELECT DISTINCT vphv.pseudonym_nzis
FROM x_nrhzs_data.v_polozka_01_hvlp vphv
WHERE (
    vphv.diagnoza LIKE 'C67%'
    OR vphv.diagnoza_polozky LIKE 'C67%'
    OR vphv.diagnoza_dokladu LIKE 'C67%'
);

CREATE TABLE x_sandbox_syntetika.ext_00098892_fnol_01_filtr_vykon AS
SELECT vpz.pseudonym_nzis
FROM x_nrhzs_data.v_polozka_00_zdravotni_vykon vpz
WHERE (
    vpz.diagnoza LIKE 'C67%'
    OR vpz.diagnoza_polozky LIKE 'C67%'
    OR vpz.diagnoza_dokladu LIKE 'C67%'
);

CREATE TABLE x_sandbox_syntetika.ext_00098892_fnol_01_filtr AS
SELECT pseudonym_nzis
FROM x_sandbox_syntetika.ext_00098892_fnol_01_filtr_hvlp
UNION
SELECT pseudonym_nzis
FROM x_sandbox_syntetika.ext_00098892_fnol_01_filtr_vykon;

CREATE TABLE x_sandbox_syntetika.ext_00098892_fnol_01_hvlp AS
SELECT
    vph.pseudonym_nzis,
    vph.mnozstvi,
    vph.cis_atc_kod AS atc_nebo_kod,
    vph.den,
    vph.pohlavi,
    vph.rok_narozeni,
    vph.icz
FROM x_nrhzs_data.v_polozka_01_hvlp vph
WHERE cis_atc_kod IN (
    'L01DC03', 'L01DB03', 'L01BC05', 'L01XA01', 'L01XA02', 'L04AX03', 'L01CA01', 'L01DB01', 'L01CD01',
    'L01CA05', 'L01FX13', 'L01FF01', 'L01FF05', 'L01FF02', 'L01FF04', 'L03AX03', 'L01FD04', 'L01BA01',
    'L01XC36', 'L01XC17', 'L01XC32', 'L01XC18', 'L01XC31', 'L01XC03', 'L01FX04', 'L01XC11'
)
AND vph.pseudonym_nzis IN (
    SELECT pseudonym_nzis
    FROM x_sandbox_syntetika.ext_00098892_fnol_01_filtr
);

CREATE TABLE x_sandbox_syntetika.ext_00098892_fnol_01_vykon AS
SELECT
    vpz.pseudonym_nzis,
    vpz.mnozstvi,
    vpz.kod_polozky AS atc_nebo_kod,
    vpz.den,
    vpz.pohlavi,
    vpz.rok_narozeni,
    vpz.icz
FROM x_nrhzs_data.v_polozka_00_zdravotni_vykon vpz
WHERE kod_polozky IN (
    -- Urology
    '76531', '76565', '76555', '76559', '76557', '76363',
    -- Oncology
    '42021', '42022', '42023', '51881', '43022', '43023', '43021',
    -- Cystectomy
    '76373', '76375', '91980', '76311', '76713', '76331', '76369', '76371', '76471', '76711',
    -- Radiotherapy
    '43210', '43213', '43215', '43217', '43219', '43311', '43313', '43315', '43317', '43319',
    '43434', '43611', '43621', '43631', '43633', '43635', '43637', '43638', '43641',
    -- Examination
    '76021', '76022', '76023'
)
AND vpz.pseudonym_nzis IN (
    SELECT pseudonym_nzis
    FROM x_sandbox_syntetika.ext_00098892_fnol_01_filtr
);

SELECT
    DENSE_RANK() OVER (ORDER BY res.pseudonym_nzis) AS pacient_id,
    res.atc_nebo_kod AS pripravek_vykon,
    TO_CHAR(res.den, 'MM/YYYY') AS datum_vykonu,
    TO_CHAR(um.DatumUmrti, 'MM/YYYY') AS rok_mesic_umrti,
    res.icz,
    CASE
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 0 AND 5 THEN '0-5 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 6 AND 10 THEN '6-10 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 11 AND 15 THEN '11-15 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 16 AND 20 THEN '16-20 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 21 AND 25 THEN '21-25 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 26 AND 30 THEN '26-30 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 31 AND 35 THEN '31-35 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 36 AND 40 THEN '36-40 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 41 AND 45 THEN '41-45 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 46 AND 50 THEN '46-50 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 51 AND 55 THEN '51-55 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 56 AND 60 THEN '56-60 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 61 AND 65 THEN '61-65 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 66 AND 70 THEN '66-70 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 71 AND 75 THEN '71-75 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 76 AND 80 THEN '76-80 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 81 AND 85 THEN '81-85 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 86 AND 90 THEN '86-90 let'
        WHEN EXTRACT(YEAR FROM res.den) - res.rok_narozeni BETWEEN 91 AND 95 THEN '91-95 let'
        ELSE 'Starší 95 let'
    END AS vekovy_interval,
    res.pohlavi,
    SUM(res.mnozstvi) AS mnozstvi,
    nor.poradi AS morfologie_poradi,
    nor.morfologie_grade,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    nor.diagnoza,
    TO_CHAR(nor.diagnoza_datum, 'MM/YYYY') AS diagnoza_datum
FROM (
    SELECT pseudonym_nzis, mnozstvi, atc_nebo_kod, den, pohlavi, rok_narozeni, icz
    FROM x_sandbox_syntetika.ext_00098892_fnol_01_hvlp
    UNION
    SELECT pseudonym_nzis, mnozstvi, atc_nebo_kod, den, pohlavi, rok_narozeni, icz
    FROM x_sandbox_syntetika.ext_00098892_fnol_01_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 (
    SELECT
        RANK() OVER (PARTITION BY nor.pseudonym_nzis ORDER BY nor.diagnoza_datum) AS poradi,
        nor.pseudonym_nzis,
        nor.morfologie_grade,
        nor.T_vysl,
        nor.N_vysl,
        nor.M_vysl,
        nor.diagnoza,
        nor.diagnoza_datum
    FROM x_nzis_data.v_nor nor
    WHERE pseudonym_nzis IN (
        SELECT pseudonym_nzis
        FROM x_sandbox_syntetika.ext_00098892_fnol_01_filtr
    )
) nor ON nor.pseudonym_nzis = res.pseudonym_nzis
GROUP BY
    res.pseudonym_nzis,
    res.atc_nebo_kod,
    res.den,
    res.rok_narozeni,
    res.pohlavi,
    nor.poradi,
    nor.morfologie_grade,
    um.DatumUmrti,
    res.icz,
    nor.T_vysl,
    nor.N_vysl,
    nor.M_vysl,
    nor.diagnoza,
    nor.diagnoza_datum
ORDER BY
    DENSE_RANK() OVER (ORDER BY res.pseudonym_nzis),
    TO_CHAR(res.den, 'MM/YYYY') DESC;

