Skapa befolkningstabell SV.DODSORSAK

Regler för att skapa befolkningstabell SV.DODSORSAK

SQL för att skapa befolkningstabell SV.DODSORSAK

declare global temporary table AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr as ( select distinct a.ddbid, upper(coalesce(b.dodorstxt, '')) as dodorstxt, c.overblivenind, a.postnr, char_length(trim(upper(coalesce(b.dodorstxt, '')))) as lgd, dense_rank() over(partition by ddbid order by char_length(trim(upper(coalesce(b.dodorstxt, '')))) desc, b.postnr asc) as nr from SV.individ a join SV.db b on a.postnr = b.postnr join SV.notering c on a.postnr = c.postnr and c.overblivenind = 0 join REG.kbsid ks on c.kbsidid = ks.kbsidid join REG.kbkalla kk on ks.kbkallaid = kk.kbkallaid join REG.kyrkbok ky on kk.kyrkbokid = ky.kyrkbokid join REG.projektfas pf on ky.projektid = pf.projektid where a.individtyp = 0 and pf.fas >= 7 ) with data not logged on commit preserve rows declare global temporary table KODICD10HDESCRIPTIONS as ( select ICD10H,listagg(DESCRIPTION,'; ') within group (order by ICD10H,DESCRIPTIONNR) as DESCRIPTION from KOD.KODICD10HDESCRIPTIONS group by ICD10H) with data not logged on commit preserve rows declare global temporary table PERSON as (select ddbid,kon, case when foddat < 16000000 then 0 when mod(foddat,10000)/100>0 and mod(foddat,100)=0 then foddat+1 when mod(foddat,10000)/100=0 and mod(foddat,100)=0 then foddat+101 when mod(foddat,10000)/100=0 and mod(foddat,100)>0 then foddat/10000*10000+101 else foddat end as foddat, case when dopdat < 16000000 then 0 when mod(dopdat,10000)/100>0 and mod(dopdat,100)=0 then dopdat+1 when mod(dopdat,10000)/100=0 and mod(dopdat,100)=0 then dopdat+101 when mod(dopdat,10000)/100=0 and mod(dopdat,100)>0 then dopdat/10000*10000+101 else dopdat end as dopdat, case when doddat < 16000000 then 0 when mod(doddat,10000)/100>0 and mod(doddat,100)=0 then case when mod(doddat,10000)/100 in (1,3,5,7,8,10,12) then doddat+31 when mod(doddat,10000)/100 in (4,6,9,11) then doddat+30 else doddat+28 end when mod(doddat,10000)/100=0 and mod(doddat,100)=0 then doddat+1231 when mod(doddat,10000)/100=0 and mod(doddat,100)>0 then doddat/10000*10000+1231 else doddat end as doddat, case when begdat < 16000000 then 0 when mod(begdat,10000)/100>0 and mod(begdat,100)=0 then case when mod(begdat,10000)/100 in (1,3,5,7,8,10,12) then begdat+31 when mod(begdat,10000)/100 in (4,6,9,11) then begdat+30 else begdat+28 end when mod(begdat,10000)/100=0 and mod(begdat,100)=0 then begdat+1231 when mod(begdat,10000)/100=0 and mod(begdat,100)>0 then begdat/10000*10000+1231 else begdat end as begdat from SV.PERSON) with data not logged on commit preserve rows declare global temporary table DBnoteringarMedStandardiseringOchKodning as ( select x.ddbid, x.dodorsnr, x.dodorsind, x.dodors, x.dodorsstd, x.qualifier, x.icd10h, coalesce(f.description,'') as icd10hbeskrivning, h.icd10desc_en as icd10beskrivning, x.cancertype as cancertyp, x.nr, x.postnr from (select a.ddbid,b.dodorsnr,c.dodorsind,b.dodors,c.dodorsstd,c.qualifier, case when d.icd10h='I64.003' and d1.datum>0 and d2.datum>0 and d2.antdag-d1.antdag<366 then 'R95.901' else d.icd10h end as icd10h, d.cancertype,a.nr,a.postnr from SESSION.AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr a join CEDAR.dodorsupp b on a.dodorstxt = b.dodorstxt join REG.dodors c on b.dodors = c.dodors join REG.dodorsstd d on c.dodorsstd = d.dodorsstd and c.qualifier = d.qualifier left join SESSION.PERSON p on a.ddbid=p.ddbid left join DDB.datdag d1 on d1.datum=(case when p.foddat>0 then p.foddat else p.dopdat end) left join DDB.datdag d2 on d2.datum=(case when p.doddat>0 then p.doddat else p.begdat end) ) x join KOD.kodicd10h e on x.icd10h = e.icd10h left join SESSION.KODICD10HDESCRIPTIONS f on e.icd10h = f.icd10h join KOD.kodicd10 h on h.icd10 = e.icd10 UNION select a.ddbid, b.dodorsnr, 1 as dodorsind, b.dodors, k.kompdodorsstd, k.kompqualifier, k.icd10hkomp, coalesce(f.description,'') as icd10hbeskrivning, h.icd10desc_en as icd10beskrivning, k.kompcancertype, a.nr, a.postnr from SESSION.AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr a join CEDAR.dodorsupp b on a.dodorstxt = b.dodorstxt join REG.dodors c on b.dodors = c.dodors join REG.dodorsstd d on c.dodorsstd = d.dodorsstd and c.qualifier = d.qualifier join KOD.kodicd10hkomp k on d.dodorsstd=k.dodorsstd and d.qualifier=k.qualifier and d.icd10h=k.icd10h join KOD.kodicd10h e on k.icd10hkomp = e.icd10h left join SESSION.KODICD10HDESCRIPTIONS f on e.icd10h = f.icd10h join KOD.kodicd10 h on h.icd10 = e.icd10 where b.dodorstxt in ( select distinct c1.dodorstxt from REG.DODORS a1 join REG.DODORSSTD b1 on a1.dodorsstd=b1.dodorsstd and a1.qualifier=b1.qualifier join CEDAR.DODORSUPP c1 on a1.dodors=c1.dodors where left(b1.icd10h,1) in ('S','T') or b1.icd10h like 'Z89%' ) and b.dodorstxt not in ( select distinct c2.dodorstxt from REG.DODORS a2 join REG.DODORSSTD b2 on a2.dodorsstd=b2.dodorsstd and a2.qualifier=b2.qualifier join CEDAR.DODORSUPP c2 on a2.dodors=c2.dodors where left(b2.icd10h,1) in ('V','W','X','Y') or (b2.icd10h like 'Z%' and b2.icd10h not like 'Z89%') ) and left (d.icd10h,1) in ('S','T') or d.icd10h like 'Z89%' UNION select a.ddbid, b.dodorsnr, 0 as dodorsind, k.dodors, k.kompdodorsstd, k.kompqualifier, k.kompkod, coalesce(f.description,'') as icd10hbeskrivning, h.icd10desc_en as icd10beskrivning, k.kompcancertype, a.nr, a.postnr from SESSION.AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr a join CEDAR.dodorsupp b on a.dodorstxt = b.dodorstxt join KOD.kodicd10hyttrekomp k on k.dodors=b.dodors join KOD.kodicd10h e on k.kompkod = e.icd10h left join SESSION.KODICD10HDESCRIPTIONS f on e.icd10h = f.icd10h join KOD.kodicd10 h on h.icd10 = e.icd10 ) with data not logged on commit preserve rows declare global temporary table PersonerMedDBnoteringarMedEndastR99 as ( select a.ddbid, a.postnr, a.nr from SESSION.DBnoteringarMedStandardiseringOchKodning a where icd10h like 'R99%' and icd10h!='R99.099' and a.postnr not in (select b.postnr from SESSION.DBnoteringarMedStandardiseringOchKodning b where icd10h not like 'R99%') ) with data not logged on commit preserve rows declare global temporary table PersonerMedDBnoteringarMedEndastR99LagstaNr as ( select ddbid, min(nr) as lagsta_nr from SESSION.PersonerMedDBnoteringarMedEndastR99 group by ddbid ) with data not logged on commit preserve rows declare global temporary table PersonerMedDBnoteringarMedEndastR99Postnr as ( select a.ddbid, a.postnr from SESSION.PersonerMedDBnoteringarMedEndastR99 a join SESSION.PersonerMedDBnoteringarMedEndastR99LagstaNr b on a.ddbid = b.ddbid and a.nr = b.lagsta_nr ) with data not logged on commit preserve rows declare global temporary table PrioriteradeDBnoteringarMedStandardiseringOchKodning as ( select * from SESSION.DBnoteringarMedStandardiseringOchKodning a where (a.postnr not in (select b.postnr from SESSION.PersonerMedDBnoteringarMedEndastR99 b) or a.postnr in (select c.postnr from SESSION.PersonerMedDBnoteringarMedEndastR99Postnr c where a.ddbid = c.ddbid)) and a.icd10h != 'R99.099' ) with data not logged on commit preserve rows declare global temporary table PrioriteradeDBnoteringarMedStandardiseringOchKodningLagstaNr as ( select ddbid, min(nr) as lagsta_nr from SESSION.PrioriteradeDBnoteringarMedStandardiseringOchKodning group by ddbid ) with data not logged on commit preserve rows declare global temporary table StandardiseradeDodsorsakerOchKodning as ( select a.ddbid, dense_rank() over(partition by a.ddbid order by a.dodorsind desc,a.dodorsnr) as dodorsnr, a.dodorsind, replace(replace(replace(replace(trim( case when char_length(qualifier) > 0 then dodorsstd||' (' || qualifier || ')' else dodorsstd end ),' | ', '|'),'| ', '|'),' |', '|'),'|', ', ') as dodorsstandard, icd10h, case when char_length(icd10hbeskrivning) > 0 then icd10hbeskrivning else icd10beskrivning end as icd10hbeskrivning, cancertyp from SESSION.PrioriteradeDBnoteringarMedStandardiseringOchKodning a join SESSION.PrioriteradeDBnoteringarMedStandardiseringOchKodningLagstaNr b on a.ddbid = b.ddbid and a.nr = b.lagsta_nr ) with data not logged on commit preserve rows declare global temporary table StandardiseradeDodsorsakerOchKodningKonkatineradeICD10H as ( select ddbid,rank()over(partition by ddbid order by ddbid,dodorsnrny,dodorsind desc) as dodorsnr, dodorsind, dodorsstandard, icd10h, icd10hbeskrivning, cancertyp from (select ddbid, min(dodorsnr) as dodorsnrny, dodorsind, listagg(distinct dodorsstandard,', ') within group (order by dodorsstandard) as dodorsstandard, icd10h, icd10hbeskrivning, cancertyp from SESSION.StandardiseradeDodsorsakerOchKodning group by ddbid, dodorsind, icd10h, icd10hbeskrivning, cancertyp) ) with data not logged on commit preserve rows declare global temporary table StandardiseradeDodsorsakerOchKodningKonkatineradeICD10H_ICD10_ICD10SE as ( select a.ddbid, a.dodorsnr, a.dodorsind, a.dodorsstandard, a.icd10h, a.icd10hbeskrivning, a.cancertyp, c.icd10, d.icd10maincatdesc_en as icd10kategoribeskrivningeng, c.icd10desc_en as icd10beskrivningeng, coalesce(e.icd10se,'') as icd10se, coalesce(f.icd10semaincatdesc_sv,'') as icd10sekategoribeskrivning, coalesce(e.icd10sedesc_sv,'') as icd10sebeskrivning from SESSION.StandardiseradeDodsorsakerOchKodningKonkatineradeICD10H a join kod.kodicd10h b on a.icd10h = b.icd10h join kod.kodicd10 c on b.icd10 = c.icd10 join kod.kodicd10maincat d on c.icd10maincat = d.icd10maincat left join kod.kodicd10se e on c.icd10se = e.icd10se left join kod.kodicd10semaincat f on e.icd10semaincat = f.icd10semaincat ) with data not logged on commit preserve rows insert into SV.DODSORSAK select * from SESSION.StandardiseradeDodsorsakerOchKodningKonkatineradeICD10H_ICD10_ICD10SE order by ddbid,dodorsnr