Skapa befolkningstabell SV.INKOMST

Tabellen SV.INKOMST innehåller information om vilka taxeringsår en individ har inkomst

Regler för att skapa befolkningstabell SV.INKOMST

Regel 1:

Tabellen genereras endast för regionlänkade församlingar, projektstatus = 9

Regel 2:

Om det finns flera poster för samma år väljs post med högst värde i TAXINKOMST i första hand och INKOMST i andra hand

Regel 3:

Värdena i komlumnerna hämtas på följande sätt

SQL för att skapa befolkningstabell SV.INKOMST

insert into SV.INKOMST with Inkomstunderlag as ( select ddbid, taxar, bsttxt, frs, dediknmn, coalesce(a.bevallmtjanst,0) as bevallmtjanst, coalesce(a.bevbolag,0) as bevbolag, coalesce(a.bevegendom,0) as bevegendom, coalesce(a.bevensktjanst,0) as bevensktjanst, coalesce(a.bevfastighet,0) as bevfastighet, coalesce(a.bevjordbruk,0) as bevjordbruk, coalesce(a.bevkapital,0) as bevkapital, coalesce(a.bevrorannan,0) as bevrorannan, coalesce(a.bevrorelse,0) as bevrorelse, coalesce(a.bevrorunderhall,0) as bevrorunderhall, coalesce(a.bevrorvag,0) as bevrorvag, coalesce(a.bevsumma,0) as bevsumma, coalesce(a.bevtaxinkomst,0) as bevtaxinkomst, coalesce(a.bevtillftjanst,0) as bevtillftjanst, coalesce(a.bevtjanst,0) as bevtjanst, coalesce(a.stattaxinkomst,0) as stattaxinkomst, coalesce(a.taxfastighet,0) as taxfastighet, coalesce(a.taxhelhet,0) as taxhelhet, listagg(distinct y1.yrkestdnmn,',') as yrkestdnmnall from SV.INDIVID i join SV.NOTERING n on n.postnr = i.postnr join SV.INKTAX a on a.postnr = i.postnr join REG.KBSID s on n.KBSIDID = s.KBSIDID join REG.KBKALLA k on s.KBKALLAID = k.KBKALLAID join REG.KYRKBOK b on k.KYRKBOKID = b.KYRKBOKID join REG.PROJEKT pr on pr.PROJEKTID = b.PROJEKTID join REG.PROJEKTFAS p on b.PROJEKTID = p.PROJEKTID and p.FAS = 9 join REG.TAXAR t on s.kbkallaid = t.kbkallaid and s.sid between t.fransidnr and t.tillsidnr join KOD.DEDIKKATALOG dk on pr.frs = dk.dedik left join SV.INDIVIDYRKE y on y.individid = i.individid left join REG.YRKEALLTXT y1 on y.yrketxt = y1.yrketxt and i.kon = y1.yrkekon where a.eftertaxering = 0 group by ddbid, taxar, bsttxt, frs, dediknmn, coalesce(a.bevallmtjanst,0), coalesce(a.bevbolag,0), coalesce(a.bevegendom,0), coalesce(a.bevensktjanst,0), coalesce(a.bevfastighet,0), coalesce(a.bevjordbruk,0), coalesce(a.bevkapital,0), coalesce(a.bevrorannan,0), coalesce(a.bevrorelse,0), coalesce(a.bevrorunderhall,0), coalesce(a.bevrorvag,0), coalesce(a.bevsumma,0), coalesce(a.bevtaxinkomst,0), coalesce(a.bevtillftjanst,0), coalesce(a.bevtjanst,0), coalesce(a.stattaxinkomst,0), coalesce(a.taxfastighet,0), coalesce(a.taxhelhet,0) ) ,AllaInkomstPoster as ( select ddbid, taxar, bsttxt, yrkestdnmnall, frs, dediknmn, case when taxar between 1903 and 1910 then coalesce(taxhelhet,0) when taxar between 1911 and 1943 then coalesce(stattaxinkomst,0) else coalesce(bevtaxinkomst,0) end as TAXINKOMST, case when taxar between 1944 and 1950 then float(BEVJORDBRUK+bevfastighet+bevbolag+bevkapital) else bevsumma end as INKOMST, bevkapital as KAPITAL, float(taxfastighet+bevegendom +bevjordbruk + bevfastighet) as INKFASTEGENDOM, bevjordbruk as INKJORDBRUK, bevfastighet as INKFASTIGHET, case when taxar between 1894 and 1897 then float(bevrorunderhall+bevrorannan) when taxar between 1898 and 1902 then float(bevrorvag+bevrorannan) when taxar between 1929 and 1950 then bevbolag else bevrorelse end as INKNARING, float(BEVALLMTJANST+BEVENSKTJANST+ BEVTJANST+BEVTILLFTJANST) as INKTJANST from Inkomstunderlag ) ,AllaInkomstPosterMedSortering as ( select rank()over(partition by ddbid,taxar order by taxinkomst desc,inkomst desc) as nr,* from AllaInkomstPoster ) select frs,dediknmn,DDBID,TAXAR,coalesce(BSTTXT,'') as BSTTXT,coalesce(YRKESTDNMNALL,'') as YRKESTDNMNALL,TAXINKOMST,INKOMST,KAPITAL,INKFASTEGENDOM,INKJORDBRUK,INKFASTIGHET,INKNARING,INKTJANST from AllaInkomstPosterMedSortering where nr=1 order by ddbid,taxar