
------------------------------------------------------------------------------------------------
-- Zátěž populace ČR kardiovaskulárními onemocněními
-- Odkaz na výstup: https://www.nzip.cz/data/1666-kardiovaskularni-onemocneni-zatez-ceska-republika   
------------------------------------------------------------------------------------------------

-- 1) Pomocný výstup: osobo-roky, kdy byla vykázaná vybraná diagnóza v hospitalizační nebo ambulantní péči
-- temp1 (37,863,915 záznamů / 2.1 GB)

create table x_sandbox_syntetika.journal_populace_kardio_zatez_temp1 as 
select pseudonym_nzis, rok_davky,
max(case when id_pripadu_bez_cizincu is not null and substring(odbornost,2,1) in ('F','H','I','P','R','S','T') then 1 else null end) as KV_hospitalizace,
max(case when odbornost in ('001','002','101','107','301','302') then 1 else null end) as KV_ambulance
from x_nrhzs_data.v_doklad
where (skupina_diagnoz_seznam like '%I0%' or skupina_diagnoz_seznam like '%I1%' or skupina_diagnoz_seznam like '%I2%'
	or skupina_diagnoz_seznam like '%I3%' or skupina_diagnoz_seznam like '%I4%' or skupina_diagnoz_seznam like '%I5%'
	or skupina_diagnoz_seznam like '%I7%' or skupina_diagnoz_seznam like '%I8%' or skupina_diagnoz_seznam like '%I9%'
	or skupina_diagnoz_seznam like '%Q2%')
	and druh_dokladu in ('01','02','05','06')
	and (substring(odbornost,2,1) in ('F','H','I','P','R','S','T') or odbornost in ('001','002','101','107','301','302'))
group by pseudonym_nzis, rok_davky


-- 2) Pomocný výstup: osobo-roky, kdy byla vykázaná vybraná léčiva
-- temp2 (50,734,437 záznamů / 1.9 GB)

create table x_sandbox_syntetika.journal_populace_kardio_zatez_temp2 as 
select pseudonym_nzis, year(den) AS rok_davky, 1 as KV_leciva
from
(
	select pseudonym_nzis, den, kod_polozky 
	from x_nrhzs_data.v_polozka_01_hvlp
	where cis_atc_kod like 'C%'
	group by pseudonym_nzis, den, kod_polozky
	having sum(mnozstvi)>0
) as AC01
group by pseudonym_nzis, year(den);


-- 3) Pomocný výstup: sloučení; osobo-roky, kdy je splněna alespoň jedna z podmínek a) nebo b)
	-- a) osoby naživu v daném roce, které byly v předcházejícím 5letém období hospitalizovány pro vybrané diagnózy
	-- b) osoby naživu v daném roce, které byly v předcházejícím 5letém období ambulantně léčeny pro vybrané diagnózy a zároveň jim byla vykázána vybraná léčiva
-- temp3 (33,908,941 záznamů / 754 MB)

create table x_sandbox_syntetika.journal_populace_kardio_zatez_temp3 as 
select pseudonym_nzis, rok_davky, pohlavi, rok_narozeni, pacient_okres
from
(
	select AB.pseudonym_nzis, AB.rok_davky, pohlavi, rok_narozeni, pacient_okres,
	max(KV_hospitalizace) as KV_hospitalizace, max(KV_ambulance) as KV_ambulance,  max(KV_leciva) as KV_leciva
	from
	( 
		select pseudonym_nzis, rok_davky, min(pohlavi) AS pohlavi, min(rok_narozeni) AS rok_narozeni, min(pacient_okres) AS pacient_okres
		from x_nrhzs_data.v_doklad
		where rok_davky between 2014 and 2023 -- Nastavení požadovaného časového intervalu pro výstup (2014 až poslední uzavřený rok)
		group by pseudonym_nzis, rok_davky
	) as AB
	left join x_sandbox_syntetika.journal_populace_kardio_zatez_temp1 as p1 on (AB2.pseudonym_nzis=p1.pseudonym_nzis and AB2.rok_davky>=p1.rok_davky and AB2.rok_davky<p1.rok_davky+5)
	left join x_sandbox_syntetika.journal_populace_kardio_zatez_temp2 as p2 on (AB2.pseudonym_nzis=p2.pseudonym_nzis and AB2.rok_davky>=p2.rok_davky and AB2.rok_davky<p2.rok_davky+5)
	group by AB.pseudonym_nzis, AB.rok_davky, pohlavi, rok_narozeni, pacient_okres
) as tf
where (KV_hospitalizace=1 or (KV_ambulance=1 and KV_leciva=1))


-- 4) Finální výstup: agregované počty osob v jednotlivých letech, členěno dle demografických charakteristik (32,809 záznamů)

CREATE TABLE x_sandbox_syntetika.JOURNAL_SQL_populace_kardio_zatez as
select rok_davky, kraj_bydliste, okres_bydliste, pohlavi, vek_kategorie, count(*) as prevalence_KVO
from
(
	select
	pseudonym_nzis, rok_davky,
	case when pacient_okres='N/A' then 'CZ099'
		 else substring(pacient_okres,1,5) end as kraj_bydliste,
	case when pacient_okres like 'CZ010%' then 'CZ0100' -- Sloučení Prahy
		 when pacient_okres='N/A' then 'CZ0999' 
		 else pacient_okres end as okres_bydliste,
	case when pohlavi='M' then 1
		 when pohlavi='Z' then 2 
		 else null end as pohlavi,
	case when (rok_davky-rok_narozeni)=0 then 0
		 when (rok_davky-rok_narozeni)>=1 and (rok_davky-rok_narozeni)<=4 then 1
		 when (rok_davky-rok_narozeni)>=5 and (rok_davky-rok_narozeni)<=9 then 2
		 when (rok_davky-rok_narozeni)>=10 and (rok_davky-rok_narozeni)<=14 then 3
		 when (rok_davky-rok_narozeni)>=15 and (rok_davky-rok_narozeni)<=19 then 4
		 when (rok_davky-rok_narozeni)>=20 and (rok_davky-rok_narozeni)<=24 then 5
		 when (rok_davky-rok_narozeni)>=25 and (rok_davky-rok_narozeni)<=29 then 6
		 when (rok_davky-rok_narozeni)>=30 and (rok_davky-rok_narozeni)<=34 then 7
		 when (rok_davky-rok_narozeni)>=35 and (rok_davky-rok_narozeni)<=39 then 8
		 when (rok_davky-rok_narozeni)>=40 and (rok_davky-rok_narozeni)<=44 then 9
		 when (rok_davky-rok_narozeni)>=45 and (rok_davky-rok_narozeni)<=49 then 10
		 when (rok_davky-rok_narozeni)>=50 and (rok_davky-rok_narozeni)<=54 then 11
		 when (rok_davky-rok_narozeni)>=55 and (rok_davky-rok_narozeni)<=59 then 12
		 when (rok_davky-rok_narozeni)>=60 and (rok_davky-rok_narozeni)<=64 then 13
		 when (rok_davky-rok_narozeni)>=65 and (rok_davky-rok_narozeni)<=69 then 14
		 when (rok_davky-rok_narozeni)>=70 and (rok_davky-rok_narozeni)<=74 then 15
		 when (rok_davky-rok_narozeni)>=75 and (rok_davky-rok_narozeni)<=79 then 16
		 when (rok_davky-rok_narozeni)>=80 and (rok_davky-rok_narozeni)<=84 then 17
		 when (rok_davky-rok_narozeni)>=85 and (rok_davky-rok_narozeni)<=89 then 18
		 when (rok_davky-rok_narozeni)>=90 and (rok_davky-rok_narozeni)<=94 then 19
		 when (rok_davky-rok_narozeni)>=95 then 20
		 else null end as vek_kategorie
	from x_sandbox_syntetika.journal_populace_kardio_zatez_temp3
) as t1
group by rok_davky, kraj_bydliste, okres_bydliste, pohlavi, vek_kategorie;
 

-- 5) Odstranění pomocných výstupů    

drop table x_sandbox_syntetika.journal_populace_kardio_zatez_temp1;
drop table x_sandbox_syntetika.journal_populace_kardio_zatez_temp2;
drop table x_sandbox_syntetika.journal_populace_kardio_zatez_temp3;
