  -- 1. VÝKONY
SELECT 
    v.pseudonym_nzis AS pacient_id,
    v.kod_polozky AS signalni_vykon,
    TO_CHAR(v.den, 'YYYY') AS rok,
    COUNT(v.kod_polozky) AS pocet_vykonu
FROM 
    x_nrhzs_data.v_polozka_00_zdravotni_vykon v
WHERE 
    v.kod_polozky IN ('99975', '99986')
    AND EXTRACT(YEAR FROM v.den) >= 2017
GROUP BY 
    v.pseudonym_nzis,
    v.kod_polozky,
    TO_CHAR(v.den, 'YYYY');



-- 2. LÉČIVA
SELECT 
 d.pseudonym_nzis AS pacient_id,
    TO_CHAR(d.den, 'YYYY-MM') AS datum,  
    d.kod_polozky AS sukl_kod,
    d.cis_atc_kod AS atc_kod,
    d.diagnoza AS diagnoza,
    SUM(d.mnozstvi) AS pocet_baleni
FROM 
    x_nrhzs_data.v_polozka_01_hvlp d
WHERE
(d.cis_atc_kod IN (
    'L03AB07','L03AB08','L03AB13','L03AX13','L04AG04','L04AG11','L04AE01','L04AX07',
    'L04AG12','L04AG06','L04AA40','L04AE03','L04AG08','L04AK02','L04AE04','L04AG03',
    'L04AX09','L04AE02','L04AD03')
  OR
  (d.cis_atc_kod = 'L01FA01'AND (d.diagnoza LIKE 'G35%' OR d.diagnoza LIKE 'M32%'))
)
    AND EXTRACT(YEAR FROM d.den) >= 2017
GROUP BY 
    d.pseudonym_nzis, 
    TO_CHAR(d.den, 'YYYY-MM'), 
    d.kod_polozky,
    d.cis_atc_kod,
    d.diagnoza,
    d.cis_atc_kod,
    d.diagnoza;
    
