select sign(count(*)) from DODSORSAK

select count(distinct ddbid) from DODSORSAK where ddbid = 0

select sign(count(distinct ddbid)) from DODSORSAK where icd10h like 'R99%'

select count(distinct ddbid) from DODSORSAK where icd10h = 'R99.099'

with temp as (
    select distinct DDBID
    from DODSORSAK
        except
    select distinct DDBID
    from sv.db d
    join sv.individ i on i.postnr=d.postnr
    join sv.notering n on n.postnr=d.postnr
    join REG.kbsid ks on n.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 i.individtyp=0 and pf.fas>=7 and n.overblivenind=0
)
select count(distinct ddbid) from temp 

with temp as (
select distinct ddbid,ICD10H, ICD10HBESKRIVNING
from DODSORSAK
where ICD10h is not NULL
except
select distinct d.ddbid,d.ICD10H, coalesce(k2.description,k3.icd10desc_en) as ICD10HBESKRIVNING
from DODSORSAK d
join kod.kodicd10h k1 on k1.icd10h=d.icd10h
left join kod.kodicd10hdescriptions k2 on k2.icd10h=k1.icd10h
left join kod.kodicd10 k3 on k3.icd10=k1.icd10
)
select count(*) from temp

with AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr as (
select distinct a.ddbid, coalesce(b.dodorstxt,'') as dodorstxt, length(trim(coalesce(b.dodorstxt,'')),CODEUNITS16) as lgd,
dense_rank()over(partition by ddbid order by length(trim(coalesce(b.dodorstxt,'')),CODEUNITS16) 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
)
,PrioriteradeDBnoteringar as (
select ddbid from AllaDBnoteringarForLankadeForsamlingarSorteradPaLangdOchPostnr where nr=1 and lgd=0
)
select count(distinct DDBID) from PrioriteradeDBnoteringar where ddbid in (select x.ddbid from DODSORSAK x)
                

with temp as (
    select distinct DDBID
    from DODSORSAK
        intersect
    select distinct DDBID
    from sv.db d
    join sv.individ i on i.postnr=d.postnr
    join sv.notering n on n.postnr=d.postnr
    join REG.kbsid ks on n.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 i.individtyp=0 and pf.fas>=7 and n.overblivenind=1
        except
    select distinct DDBID
    from sv.db d
    join sv.individ i on i.postnr=d.postnr
    join sv.notering n on n.postnr=d.postnr
    join REG.kbsid ks on n.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 i.individtyp=0 and pf.fas>=7 and n.overblivenind=0
)
select count(distinct ddbid) from temp
                

with temp as (
select distinct a.ddbid, coalesce(b.dodorstxt,'') as dodorstxt, a.postnr, length(trim(coalesce(b.dodorstxt,'')),CODEUNITS16) as lgd,
dense_rank()over(partition by ddbid order by length(trim(coalesce(b.dodorstxt,'')),CODEUNITS16) desc,b.postnr asc) as nr,
trim(case when length(d2.qualifier,CODEUNITS16)>0 then d2.dodorsstd||' ('||d2.qualifier||')' else d2.dodorsstd end) as dodorsstandard,d3.icd10h
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
join CEDAR.dodorsupp d1 on b.dodorstxt=d1.dodorstxt
join REG.dodors d2 on d1.dodors=d2.dodors
join REG.dodorsstd d3 on d2.dodorsstd=d3.dodorsstd and d2.qualifier=d3.qualifier
where a.individtyp=0 and pf.fas>=7
)
,temp2 as (
select ddbid,dodorsstandard,icd10h from temp where ddbid in (10659185,18224361) and nr=1
except
select ddbid,dodorsstandard,icd10h from DODSORSAK where ddbid in (10659185,18224361)
)
select count(*) from temp2
                

with temp as (
    SELECT a.icd10, b.icd10h, c.description as ICD10HDESCRIPTION_orig
    , a.icd10maincat, a.icd10se, a.icd10desc_en
    FROM "KOD"."KODICD10" a
    left join kod.kodicd10h b on b.icd10=a.icd10
    left join kod.KODICD10HDESCRIPTIONS c on c.icd10h=b.icd10h
)
,ICD10HDESCRIPTIONExtendedWithModernICD10 as (
    select icd10, icd10h, ICD10HDESCRIPTION_orig,
    case when ICD10HDESCRIPTION_orig is not null then ICD10HDESCRIPTION_orig
         when ICD10HDESCRIPTION_orig is null then ICD10DESC_EN
         else 'SAKNAS'
    end as ICD10HDESCRIPTION_NEW,
    icd10maincat, icd10se, icd10desc_en
    from temp
)
,ValideringRegel7 as (
    select a.icd10, a.icd10h,a.ICD10HDESCRIPTION_orig, a.ICD10HDESCRIPTION_NEW, b.ICD10hbeskrivning
    from ICD10HDESCRIPTIONExtendedWithModernICD10 a
    join DODSORSAK b on b.ICD10h=a.ICD10H
    where ICD10HDESCRIPTION_ORIG is null and ICD10HDESCRIPTION_NEW!=ICD10HBESKRIVNING
)
select count(*)
from ValideringRegel7

select count(*) from (select ddbid,icd10h,count(*) from sv.dodsorsak group by ddbid,icd10h having count(*)>1)