set trimspool on
--set serveroutput on size 100000;
set termout oFF;
set verify off;
set pagesize 0;
set heading off;
set feedback off;
set linesize700;
alter session set nls_date_format='dd-mm-yyyy';
------------------------------------------------------------------------
-- Tabel 1. Rekapitulasi Jumlah Menurut Kecamatan dan Jenis Kelamin
------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas01 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC    
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 else null end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/
     (select count(NIK) from biodata_wni where flag_status='0'),2) PLK
    ,sum (case when jenis_klmin in(2) then 1 else null end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/
     (select count(NIK) from biodata_wni where flag_status='0'),2) PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else null end) JMLJK
    ,round(count(NIK)*100/
     (select count(NIK) from biodata_wni where flag_status='0'),2)  PJK
FROM biodata_wni WHERE flag_status='0'
GROUP BY 
GROUPING SETS ((no_kec,no_kab,no_prop)
,(NO_PROP,NO_KAB)
)
ORDER BY NO_PROP,NO_KAB,NO_KEC;
set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas01.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JMLJK||'#'||PJK
from &1..xkuantitas01
order by NOKEC;

spool off;
--------------------------------------------------------------------
-- Tabel 2. Penduduk Menurut Luas Wilayah dan Kepadatan
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas02 NOLOGGING TABLESPACE SIAKDATA AS
SELECT NOKEC,NMKEC,SUM(JDUK) JDUK,SUM(LWIL) LWIL, AVG(KPDT) KPDT 
FROM
(
SELECT 
    trim(to_char(a.no_prop,'00'))||''||trim(to_char(a.no_kab,'00'))||''||trim(to_char(a.no_kec,'00'))NOKEC
   ,getnamakec(a.no_kec,a.no_kab,a.no_prop) NMKEC
   ,sum (case when a.nik is not null then 1 else 0 end) JDUK
   ,b.luas as LWIL
   ,round(sum (case when a.nik is not null then 1 else 0 end)/b.luas,0) as KPDT
FROM biodata_wni a 
        inner join luaskodwil b ON 
                a.no_kec=b.no_kec and 
                a.flag_status='0'
GROUP BY a.no_kec,a.no_kab,a.no_prop,b.luas
)
GROUP BY 
GROUPING SETS((NOKEC,NMKEC,JDUK,LWIL,KPDT)
,()
)
ORDER BY NOKEC;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas02.csv;
select 
NOKEC
||'#'||NMKEC||'#'||JDUK||'#'||LWIL||'#'||KPDT
from &1..xkuantitas02
order by NOKEC;

spool off;
--------------------------------------------------------------------
---Tabel 3. Angka Tertumbuhan Rasio Penduduk Berdasarkan Kecamatan
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas03 NOLOGGING TABLESPACE SIAKDATA AS
select
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 else null end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/ (select count(NIK) from biodata_wni where flag_status='0'),2) PLK
    ,sum (case when jenis_klmin in(2) then 1 else null end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/ (select count(NIK) from biodata_wni where flag_status='0'),2) PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else null end) JMLJK
    ,round(count(NIK)*100/ (select count(NIK) from biodata_wni where flag_status='0'),2)as  PJK,
    (select count(nik) from biodata_wni_201602 b where flag_status=0 and b.no_prop=a.no_prop and b.no_kab=a.no_kab and b.no_kec=a.no_kec)as TBACK
     ,round(count(NIK)*100/ (select count(NIK) from biodata_wni_201602 where flag_status='0'),2)as  PTBACK
from biodata_wni a where flag_status='0'
group by 
GROUPING SETS((a.no_kec,a.no_kab,a.no_prop)
,(a.no_prop,a.no_kab)
)
order by a.no_prop,a.no_kab,a.no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas03.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JMLJK||'#'||PJK||'#'||TBACK||'#'||PTBACK
from &1..xkuantitas03
order by NOKEC;

spool off;
--------------------------------------------------------------------
---Tabel 4. Proporsi Penduduk Menurut Kelompok Umur dan Jenis Kelamin
--------------------------------------------------------------------
CREATE TABLE &1..xkuantitas04 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    row_number() over(partition by no_kab order by no_kab) as norut
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','> 75') as UMUR
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JML
    ,round(count(*)*100/(select count(*) from biodata_wni where flag_status='0'),2) PJK
FROM biodata_wni
WHERE tgl_lhr < '31-DEC-2021' and flag_status='0'
GROUP BY
grouping sets(( 
    no_kab,no_prop,
    decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60)),
    0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','> 75'))
    ,(no_kab,no_prop)
    )
ORDER BY no_kab,norut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas04.csv;
select 
NORUT
||'#'||UMUR||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JML||'#'||PJK
from &1..xkuantitas04
order by NORUT;

spool off;
----------------------------------------------------------------------------------------------------
--Tabel 5. Proporsi Penduduk Menurut Kelompok Umur, Jenis Kelamin dan Rasio Jenis Kelamin (Sex Rasio)
----------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas05 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as norut
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as umur
    ,sum (case when jenis_klmin in(1) then 1 else null end) LK
    ,round(count (decode (jenis_klmin, 1, 1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else null end) PR
    ,round(count (decode (jenis_klmin, 2, 1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else null end) JMLJK
    ,round((count(decode(jenis_klmin,1,1))/count(decode(jenis_klmin,2,1)))*100,2) RASIO
FROM biodata_wni 
WHERE tgl_lhr < '31-DEC-2021' and flag_status='0'
GROUP BY
grouping sets ((no_kab,no_prop,
    decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60)),
    0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75'))
    ,(no_kab,no_prop)
      )
ORDER BY no_kab,norut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas05.csv;
select 
NORUT
||'#'||UMUR||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JMLJK||'#'||RASIO
from &1..xkuantitas05
order by NORUT;

spool off;
---------------------------------------------------------------------
--Tabel 6a. Penduduk Menurut Kecamatan, Jenis Kelamin dan Umur Median
---------------------------------------------------------------------
CREATE TABLE &1..xkuantitas06a NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 eLse 0 end) LK
     ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PLK
     ,sum (case when jenis_klmin in(2) then 1 eLse 0 end) PR
     ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PPR
    ,sum (case when jenis_klmin in(1,2) then 1 ELse 0 end) JML
    ,round(count(nik)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PJK
    ,median(floor(months_between('31-DEC-2021',tgl_lhr)/12)) as UMEDIAN 
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas06a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JML||'#'||PJK||'#'||UMEDIAN
from &1..xkuantitas06a
order by NOKEC;

spool off;
---------------------------------------------------------------------------
--Tabel 6. Penduduk Menurut Kecamatan, Jenis Kelamin dan Rasio Jenis Kelamin
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas06 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 eLse 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PLK
    ,sum (case when jenis_klmin in(2) then 1 eLse 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PPR
    ,sum (case when jenis_klmin in(1,2) then 1 ELse 0 end) JML
    ,round(count(*)*100/(select count(*) from biodata_wni where flag_status='0'),2) PJK
    ,round((count(decode(jenis_klmin,1,1))/count(decode(jenis_klmin,2,1)))*100,2) RASIO
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas06.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JML||'#'||PJK||'#'||RASIO
from &1..xkuantitas06
order by NOKEC;

spool off;
--------------------------------------------------------------------------------
--Tabel 7a. Penduduk Menurut Kecamatan, Muda (0-14), Produktif(15-64) dan Tua(>=65)
--------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas07a NOLOGGING TABLESPACE SIAKDATA AS
select
'1' as URUT
,'0-14 Tahun' as UMUR 
,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 14 then 1 else 0 end) LK
,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 14 then 1 else 0 end) PR
,sum (case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 14 then 1 else 0 end) JML
,round(sum(case when jenis_klmin in(1,2)   and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 14 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PJK  
FROM BIODATA_WNI where flag_status='0'
group by no_kab
union
select
'2' as URUT
,'15-64 Tahun' as UMUR 
,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 then 1 else 0 end) LK
,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 then 1 else 0 end) PR
,sum (case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 then 1 else 0 end) JML
,round(sum(case when jenis_klmin in(1,2)  and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PJK  
FROM BIODATA_WNI where flag_status='0'
group by no_kab
union
select
'3' as URUT
,'>=65 Tahun' as UMUR 
,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=65 then 1 else 0 end) LK
,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=65 then 1 else 0 end) PR
,sum (case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=65 then 1 else 0 end) JML
,round(sum(case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=65 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PJK  
FROM BIODATA_WNI where flag_status='0'
group by no_kab
union
select
'4' as URUT
,'TOTAL' as UMUR 
,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0 then 1 else 0 end) LK
,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0 then 1 else 0 end) PR
,sum (case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0 then 1 else 0 end) JML
,round(sum(case when jenis_klmin in(1,2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PJK  
FROM BIODATA_WNI where flag_status='0'
group by no_kab;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas07a.csv;
select 
URUT
||'#'||UMUR||'#'||LK||'#'||PR||'#'||JML||'#'||PJK
from &1..xkuantitas07a
order by URUT;

spool off;
--------------------------------------------------------------------------
--Tabel 7. Penduduk Menurut Kecamatan Persentase Rasio Usia Produktif(15-64) 
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas07 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
     ,getnamakec(no_kec,no_kab,no_prop) NMKEC
     ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 and jenis_klmin in(1) then 1 else 0 end) LD1564
     ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 and jenis_klmin in(2) then 1 else 0 end) PD1564
     ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end) JJK1564
     ,round((sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100)/
      (select count(nik) from biodata_wni where flag_status='0'),2) PLPD1564      
     ,round((sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 and jenis_klmin in(1) then 1 else 0 end)+
     sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end))/ 
     sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) RASIO
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas07.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LD1564||'#'||PD1564||'#'||JJK1564||'#'||PLPD1564||'#'||RASIO
from &1..xkuantitas07
order by NOKEC;


spool off;
--------------------------------------------------------------------------
--GARPHICS 7b. Penduduk Menurut Kecamatan Persentase Rasio Usia Produktif(15-64) 
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas07b NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
     trim(to_char(no_kec,'00')) NOKEC
		 ,getnamakec(no_kec,no_kab,no_prop) NMKEC
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 and jenis_klmin in(1) then 1 else 0 end) LM0014
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 and jenis_klmin in(2) then 1 else 0 end) PM0014
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end) JM0014	 
		 ,round(sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)*100/(select count(nik) from biodata_wni),2) PLPM0014 
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 and jenis_klmin in(1) then 1 else 0 end) LD1564
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 and jenis_klmin in(2) then 1 else 0 end) PD1564
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end) JD1564	 
		 ,round(sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100/(select count(nik) from biodata_wni),2) PLPD1564
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 and jenis_klmin in(1) then 1 else 0 end) LT65UP
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 and jenis_klmin in(2) then 1 else 0 end) PT65UP
		 ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 then 1 else 0 end) JT65UP	 
		 ,round(sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 then 1 else 0 end)*100/(select count(nik) from biodata_wni),2) PLPT65UP
FROM biodata_wni where flag_status='0'
GROUP BY no_prop,no_kab,no_kec
ORDER BY no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas07b.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LM0014||'#'||PM0014||'#'||JM0014||'#'||PLPM0014||'#'||LD1564||'#'||PD1564||'#'||JD1564||'#'||PLPD1564||'#'||LT65UP||'#'||PT65UP||'#'||JT65UP||'#'||PLPT65UP
from &1..xkuantitas07b
order by NOKEC;





spool off;
------------------------------------------------
--Tabel 8a. Rasio Ketergantungan Menurut Kecamatan
------------------------------------------------
CREATE TABLE &1..xkuantitas08a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
        ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)/
        sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) as MJK
        ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65  then 1 else 0 end)/
        sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) BETWEEN 15 AND 64 then 1 else 0 end)*100,2) as TJK
        ,round((sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)+
         sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 then 1 else 0 end))/
         sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) PJ1465 
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas08a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||MJK||'#'||TJK||'#'||PJ1465
from &1..xkuantitas08a
order by NOKEC;

spool off;
----------------------------------------------------
--Tabel 8b. Rasio Ketergantungan Menurut Jenis Kelamin
----------------------------------------------------
CREATE TABLE &1..xkuantitas08b NOLOGGING TABLESPACE SIAKDATA AS
SELECT
       row_number() over(partition by no_kab order by no_kab) as URUT
       ,DECODE(jenis_klmin,1,'LAKI-LAKI',2,'PEREMPUAN','L + P') NMJK
       ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)/
        sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) as MJK
       ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65  then 1 else 0 end)/
        sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) BETWEEN 15 AND 64 then 1 else 0 end)*100,2) as TJK
       ,round((sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)+
        sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 then 1 else 0 end))/
        sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) PJ1465 
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS ((jenis_klmin,no_kab)
,(no_kab)
)
ORDER BY urut,jenis_klmin;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas08b.csv;
select 
URUT
||'#'||NMJK||'#'||MJK||'#'||TJK||'#'||PJ1465
from &1..xkuantitas08b
order by URUT;

spool off;
------------------------------------------------------------------
--Tabel 8. Rasio Ketergantungan Menurut Kecamatan dan Jenis Kelamin
------------------------------------------------------------------
CREATE TABLE &1..xkuantitas08 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 and jenis_klmin in(1) then 1 else 0 end) LM14
    ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 and jenis_klmin in(2) then 1 else 0 end) PM14
    ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14  then 1 else 0 end)/
     sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64  then 1 else 0 end)*100,2) as MPR
    ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 and jenis_klmin in(1) then 1 else 0 end) LT65
    ,sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 and jenis_klmin in(2) then 1 else 0 end) PT65
    ,round(sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65  then 1 else 0 end)/
     sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) BETWEEN 15 AND 64  then 1 else 0 end)*100,2) as TPR
    ,sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)+
     sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) >= 65 then 1 else 0 end) AS J1465
    ,round((sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 0 and 14 then 1 else 0 end)+
     sum(case when floor (months_between ('31-DEC-2021', tgl_lhr)/12)>=65 then 1 else 0 end))/
     sum (case when floor (months_between ('31-DEC-2021', tgl_lhr)/12) between 15 and 64 then 1 else 0 end)*100,2) PJ1465 
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS ((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas08.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LM14||'#'||PM14||'#'||MPR||'#'||LT65||'#'||PT65||'#'||TPR||'#'||J1465||'#'||PJ1465
from &1..xkuantitas08
order by NOKEC;

spool off;
---------------------------------------------------------------------------------------
--Tabel 9a. Penduduk Menurut Kecamatan, Pendidikan ditammatkan berdasarkan Jenis Kelamin
---------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas09a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.pddk_akh NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni),2) PDUK
FROM biodata_wni a, ref_siak_wni b where a.pddk_akh=b.no and a.flag_status='0' and b.sect='101'
and floor(months_between('31-DEC-2021',a.tgl_lhr)/12)>=7
GROUP BY 
grouping sets((no_prop,no_kab,a.pddk_akh,b.descrip)
,(no_prop,no_kab)
)
ORDER BY no_kab,a.pddk_akh;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas09a.csv;
select 
NPD
||'#'||NMPD||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas09a
order by NPD;

spool off;
---------------------------------------------------------------------------------
Tabel 9b. Penduduk Usia Putus Sekolah Menurut Kecamatan Berdasarkan Jenis Kelamin
---------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas09b NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where pddk_akh in(1)),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where pddk_akh in(1)),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni where pddk_akh in(1)),2) PDUK
FROM biodata_wni where flag_status='0'
and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 7 and 16 and pddk_akh in(1)
GROUP BY 
grouping sets((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas09b.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas09b
order by NOKEC;

spool off;
--------------------------------------------------
-- Tabel 10. Penduduk menurut Kecamatan dan Agama
--------------------------------------------------
CREATE TABLE &1..xkuantitas10a NOLOGGING TABLESPACE SIAKDATA AS
select 
trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) as NOKEC
,getnamakec(no_kec,no_kab,no_prop) NMKEC
,sum (case when agama in(1) then 1 else 0 end) AG1
,sum (case when agama in(2) then 1 else 0 end) AG2
,sum (case when agama in(3) then 1 else 0 end) AG3
,sum (case when agama in(4) then 1 else 0 end) AG4
,sum (case when agama in(5) then 1 else 0 end) AG5
,sum (case when agama in(6) then 1 else 0 end) AG6
,sum (case when agama in(7) then 1 else 0 end) AG7
,round(sum (case when agama in(7) then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PAG7
,sum (case when agama between 1 and 7 then 1 else 0 end) JAGM
,round(sum (case when agama between 1 and 7 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PAGM
from biodata_wni where flag_status='0'
group by 
grouping sets((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
order by no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas10a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||AG1||'#'||AG2||'#'||AG3||'#'||AG4||'#'||AG5||'#'||AG6||'#'||AG7||'#'||PAG7||'#'||JAGM||'#'||PAGM
from &1..xkuantitas10a
order by NOKEC;

spool off;
--------------------------------------------------
Tabel 10. Penduduk Menurut Agama dan Jenis Kelamin
--------------------------------------------------
CREATE TABLE &1..xkuantitas10 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.agama NAG,b.descrip NMAG
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(NIK) from biodata_wni where flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(NIK) from biodata_wni where flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni where flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b where a.agama=b.no and a.flag_status='0' and b.sect=501
GROUP BY 
grouping sets((a.agama,b.descrip)
,()
)
ORDER BY a.agama;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas10.csv;
select 
NAG
||'#'||NMAG||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas10
order by NAG;

spool off;
---------------------------------------------------------------------------
--Tabel 11. Penduduk Menurut Kecamatan, Status Perkawinan dan Jenis Kelamin
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas11 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.stat_kwn NKWN,b.descrip NMKWN
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni where flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b where a.stat_kwn=b.no and b.sect='601' and a.flag_status='0'
GROUP BY 
grouping sets((a.stat_kwn,b.descrip)
,()
)
ORDER BY a.stat_kwn;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas11.csv;
select 
NKWN
||'#'||NMKWN||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas11
order by NKWN;

spool off;
---------------------------------------------------------------------------
--Tabel 11a. Proporsi Penduduk Menurut Kelompok Umur dan Status Perkawinan
---------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas11a NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','> 75') as UMUR
    ,sum (case when stat_kwn in(1) then 1 else 0 end ) BKWN
    ,round(count(decode(stat_kwn,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PBKWN
    ,sum (case when stat_kwn in(2) then 1 else 0 end ) SKWN
    ,round(count(decode(stat_kwn,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PSKWN
    ,sum (case when stat_kwn in(3) then 1 else 0 end ) HKWN
    ,round(count(decode(stat_kwn,3,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PHKWN
    ,sum (case when stat_kwn in(4) then 1 else 0 end ) MKWN
    ,round(count(decode(stat_kwn,4,1))*100/(select count(*) from biodata_wni where flag_status='0'),2) PMKWN
    ,sum (case when stat_kwn in(1,2,3,4) then 1 else 0 end ) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni where flag_status='0'),2) PJDUK
FROM biodata_wni WHERE tgl_lhr < '31-DEC-2021' and flag_status='0'
GROUP BY
GROUPING SETS((no_kab,no_prop
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','> 75'))
,(no_kab,no_prop)    
)    
ORDER BY no_kab,urut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas11a.csv;
select 
URUT
||'#'||UMUR||'#'||BKWN||'#'||PBKWN||'#'||SKWN||'#'||PSKWN||'#'||HKWN||'#'||PHKWN||'#'||MKWN||'#'||PMKWN||'#'||JDUK||'#'||PJDUK
from &1..xkuantitas11a
order by URUT;

spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b1. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas12b1 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
    ,sum(case when pddk_akh in(1) and jenis_klmin in(1) then 1 else 0 end) LPD1
    ,sum(case when pddk_akh in(1) and jenis_klmin in(2)  then 1 else 0 end) PPD1
    ,sum(case when pddk_akh in(1) and jenis_klmin in(1,2)  then 1 else 0 end) JPD1
    ,sum(case when pddk_akh in(2) and jenis_klmin in(1) then 1 else 0 end) LPD2
    ,sum(case when pddk_akh in(2) and jenis_klmin in(2)  then 1 else 0 end) PPD2
    ,sum(case when pddk_akh in(2) and jenis_klmin in(1,2)  then 1 else 0 end) JPD2
    ,sum(case when pddk_akh in(3) and jenis_klmin in(1) then 1 else 0 end) LPD3
    ,sum(case when pddk_akh in(3) and jenis_klmin in(2)  then 1 else 0 end) PPD3
    ,sum(case when pddk_akh in(3) and jenis_klmin in(1,2)  then 1 else 0 end) JPD3
from biodata_wni   WHERE tgl_lhr < '31-DEC-2021' and flag_status='0' and pddk_akh in(1,2,3)
GROUP BY
GROUPING SETS((no_kab,no_prop
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75'))
,(no_kab,no_prop)    
)    
ORDER BY no_kab,urut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas12b1.csv;
select 
URUT
||'#'||UMUR||'#'||LPD1||'#'||PPD1||'#'||JPD1||'#'||LPD2||'#'||PPD2||'#'||JPD2||'#'||LPD3||'#'||PPD3||'#'||JPD3
from &1..xkuantitas12b1
order by URUT;

spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b2. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas12b2 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as urut
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
    ,sum(case when pddk_akh in(4) and jenis_klmin in(1) then 1 else 0 end) LPD4
    ,sum(case when pddk_akh in(4) and jenis_klmin in(2)  then 1 else 0 end) PPD4
    ,sum(case when pddk_akh in(4) and jenis_klmin in(1,2)  then 1 else 0 end) JPD4
    ,sum(case when pddk_akh in(5) and jenis_klmin in(1) then 1 else 0 end) LPD5
    ,sum(case when pddk_akh in(5) and jenis_klmin in(2)  then 1 else 0 end) PPD5
    ,sum(case when pddk_akh in(5) and jenis_klmin in(1,2)  then 1 else 0 end) JPD5
    ,sum(case when pddk_akh in(6) and jenis_klmin in(1) then 1 else 0 end) LPD6
    ,sum(case when pddk_akh in(6) and jenis_klmin in(2)  then 1 else 0 end) PPD6
    ,sum(case when pddk_akh in(6) and jenis_klmin in(1,2)  then 1 else 0 end) JPD6
from biodata_wni   WHERE tgl_lhr < '31-DEC-2021' and flag_status='0' and pddk_akh in(4,5,6)
GROUP BY
GROUPING SETS((no_kab,no_prop
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75'))
,(no_kab,no_prop)    
)    
ORDER BY no_kab,urut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas12b2.csv;
select 
URUT
||'#'||UMUR||'#'||LPD4||'#'||PPD4||'#'||JPD4||'#'||LPD5||'#'||PPD5||'#'||JPD5||'#'||LPD6||'#'||PPD6||'#'||JPD6
from &1..xkuantitas12b2
order by URUT;

spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b3. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas12b3 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as URUT
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
 	,sum(case when pddk_akh in(7) and jenis_klmin in(1) then 1 else 0 end) LPD7
	,sum(case when pddk_akh in(7) and jenis_klmin in(2)  then 1 else 0 end) PPD7
	,sum(case when pddk_akh in(7) and jenis_klmin in(1,2)  then 1 else 0 end) JPD7
	,sum(case when pddk_akh in(8) and jenis_klmin in(1) then 1 else 0 end) LPD8
	,sum(case when pddk_akh in(8) and jenis_klmin in(2)  then 1 else 0 end) PPD8
	,sum(case when pddk_akh in(8) and jenis_klmin in(1,2)  then 1 else 0 end) JPD8
	,sum(case when pddk_akh in(9) and jenis_klmin in(1) then 1 else 0 end) LPD9
	,sum(case when pddk_akh in(9) and jenis_klmin in(2)  then 1 else 0 end) PPD9
	,sum(case when pddk_akh in(9) and jenis_klmin in(1,2)  then 1 else 0 end) JPD9
from biodata_wni WHERE tgl_lhr < '31-DEC-2021' and flag_status='0' and pddk_akh in(7,8,9)
GROUP BY
GROUPING SETS((no_kab,no_prop
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75'))
,(no_kab,no_prop)    
)    
ORDER BY no_kab,urut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas12b3.csv;
select 
URUT
||'#'||UMUR||'#'||LPD7||'#'||PPD7||'#'||JPD7||'#'||LPD8||'#'||PPD8||'#'||JPD8||'#'||LPD9||'#'||PPD9||'#'||JPD9
from &1..xkuantitas12b3
order by URUT;

spool off;
------------------------------------------------------------------------------------------------
--Tabel 12 b4. Proporsi Penduduk Menurut Kelompok Umur, Pendidikan Ditammatkan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas12b4 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as URUT
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75') as UMUR
	,sum(case when pddk_akh in(10) and jenis_klmin in(1) then 1 else 0 end) LPD10
	,sum(case when pddk_akh in(10) and jenis_klmin in(2)  then 1 else 0 end) PPD10
	,sum(case when pddk_akh in(10) and jenis_klmin in(1,2)  then 1 else 0 end) JPD10
	,sum(case when pddk_akh > 0 and jenis_klmin in(1) then 1 else 0 end) JLPD
	,sum(case when pddk_akh > 0 and jenis_klmin in(2)  then 1 else 0 end) JPPD
	,sum(case when pddk_akh > 0 and jenis_klmin in(1,2)  then 1 else 0 end) JLPPD
    ,'-' as KET
from biodata_wni WHERE tgl_lhr < '31-DEC-2021' and flag_status='0' 
GROUP BY
GROUPING SETS((no_kab,no_prop
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39'
    ,8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>=75'))
,(no_kab,no_prop)    
)    
ORDER BY no_kab,urut;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas12b4.csv;
select 
URUT
||'#'||UMUR||'#'||LPD10||'#'||PPD10||'#'||JPD10||'#'||JLPD||'#'||JPPD||'#'||JLPPD||'#'||KET
from &1..xkuantitas12b4
order by URUT;

spool off;
--------------------------------------------------------------------------------
--Tabel 12. Penduduk Menurut Kecamatan, Pendidikan ditammatkan dan Jenis Kelamin
--------------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas12 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.pddk_akh NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(*) from biodata_wni where flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni where flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b where a.pddk_akh=b.no and a.flag_status='0' and sect='101'
GROUP BY 
grouping sets((no_prop,no_kab,a.pddk_akh,b.descrip)
,(no_prop,no_kab)
)
ORDER BY no_kab,a.pddk_akh;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas12.csv;
select 
NPD
||'#'||NMPD||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas12
order by NPD;

spool off;
--------------------------------------------------------------------------
--Tabel 13. Penduduk Menurut Kecamatan, Jenis Kecacatan dan Jenis Kelamin
--------------------------------------------------------------------------
CREATE TABLE &1..xkuantitas13 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.pnydng_cct NPC,b.descrip NMPC
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(select count(nik) from biodata_wni where flag_status='0'),4)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(select count(nik) from biodata_wni where flag_status='0'),4)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(nik)*100/(select count(nik) from biodata_wni where flag_status='0'),4) PDUK
FROM biodata_wni a, ref_siak_wni b where a.pnydng_cct=b.no and b.sect='701' and a.flag_status='0'
GROUP BY 
grouping sets((a.pnydng_cct,b.descrip)
,(no_kab)
)
ORDER BY a.pnydng_cct;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkuantitas13.csv;
select 
NPC
||'#'||NMPC||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkuantitas13
order by NPC;

spool off;
-------------------------------------------------------------------------------
--Tabel 14. Penduduk, Keluarga dan Rata-Rata Anggota Keluarga Menurut Kecamatan
-------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas14 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
    ,getnamakec(no_kec,no_kab,no_prop)NMKEC
    ,sum (case when nik is not null then 1 else 0 end)  JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni where flag_status='0'),2) PJDUK
    ,sum (case when stat_hbkel in(1) then 1 else 0 end) JKK
    ,round(count(decode(stat_hbkel,1,1))*100/(select count(decode(stat_hbkel,1,1)) from biodata_wni where flag_status='0'),2) PJKK
    ,round(sum (case when nik is not null then 1 else 0 end)/
	 sum (case when stat_hbkel in(1) then 1 else 0 end),2) RAVG
FROM biodata_wni  
	where flag_status='0'
GROUP BY 
GROUPING SETS((no_kec,no_kab,no_prop)
			 ,(no_kab,no_prop)
			  )
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas14.csv;
select 
NOKEC
||'#'||NMKEC||'#'||JDUK||'#'||PJDUK||'#'||JKK||'#'||PJKK||'#'||RAVG
from &1..xkualitas14
order by NOKEC;

spool off;
-----------------------------------------------------------------------------------------
--Tabel 15. Penduduk Kecamatan, Status Hubungan dengan Kepala Keluarga dan Jenis Kelamin
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas15 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.stat_hbkel NHK,b.descrip NMHK
    ,sum (case when a.jenis_klmin in(1) then 1 else 0 end) LK
    ,sum (case when a.jenis_klmin in(2) then 1 else 0 end) PR
    ,sum (case when a.jenis_klmin in(1,2) then 1 else 0 end) JDUK
FROM biodata_wni a, ref_siak_wni b 
	where 
		a.stat_hbkel=b.no and 
		b.sect='301' and 
		a.flag_status='0'
GROUP BY 
grouping sets((a.stat_hbkel,b.descrip)
,()
)
ORDER BY a.stat_hbkel;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas15.csv;
select 
NHK
||'#'||NMHK||'#'||LK||'#'||PR||'#'||JDUK
from &1..xkualitas15
order by NHK;

spool off;
---------------------------------------------------------------
--Tabel 16. Kepala Keluarga Menurut Kecamatan dan Jenis Kelamin
---------------------------------------------------------------
CREATE TABLE &1..xkualitas16 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when stat_hbkel in(1) and jenis_klmin in(1) then 1 else 0 end) LKK
    ,round(count(decode(stat_hbkel,1,decode(jenis_klmin,1,1)))*100/
     (select count(decode(stat_hbkel,1,1)) from biodata_wni where flag_status='0'),2) PLKK
    ,sum (case when stat_hbkel in(1) and jenis_klmin in(2) then 1 else 0 end) PKK
    ,round(count(decode(stat_hbkel,1,decode(jenis_klmin,2,1)))*100/
     (select count(decode(stat_hbkel,1,1)) from biodata_wni where flag_status='0'),2) PPKK
    ,sum (case when stat_hbkel in(1) then 1 else 0 end) JLPKK
    ,round(count(decode(stat_hbkel,1,1))*100/
     (select count(decode(stat_hbkel,1,1)) from biodata_wni where flag_status='0'),2) PLPKK
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_Prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas16.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LKK||'#'||PLKK||'#'||PKK||'#'||PPKK||'#'||JLPKK||'#'||PLPKK
from &1..xkualitas16
order by NOKEC;

spool off;
----------------------------------------------------------------------------------
--Tabel 17. Kepala Keluarga Menurut Kecamatan, Status Perkawinan dan Jenis Kelamin
----------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas17 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.stat_kwn NKWN,b.descrip NMKWN
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/
     (select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/
     (select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b where a.stat_kwn=b.no and b.sect='601' and a.flag_status='0' and a.stat_hbkel=1
GROUP BY 
grouping sets((a.stat_kwn,b.descrip)
,()
)
ORDER BY a.stat_kwn;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas17.csv;
select 
NKWN
||'#'||NMKWN||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkualitas17
order by NKWN;

spool off;
----------------------------------------------------------------------------------
---Tabel 18. Proporsi Kepala Keluarga Menurut Kelompok Umur dan Status Perkawinan
----------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas18 NOLOGGING TABLESPACE SIAKDATA AS
select 
    row_number() over(partition by no_kab order by no_kab) as URUT   
   ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
    ,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75') as UMUR    
    ,sum (case when stat_kwn in(1) then 1 else 0 end) KBK
    ,round(count(decode(stat_kwn,1,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKBK
    ,sum (case when stat_kwn in(2) then 1 else 0 end) KSK
    ,round(count(decode(stat_kwn,2,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKSK
    ,sum (case when stat_kwn in(3) then 1 else 0 end) KCH
    ,round(count(decode(stat_kwn,3,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKCH
    ,sum (case when stat_kwn in(4) then 1 else 0 end) KCM
    ,round(count(decode(stat_kwn,4,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKCM
    ,count (NIK) as JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PJDUK
FROM biodata_wni WHERE tgl_lhr < '31-DEC-2021' and flag_status='0'
and stat_hbkel=1 and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=15
group by 
grouping sets((no_prop,no_kab
      ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
    ,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75'))
,(no_prop,no_kab)
)   
order by no_prop,no_kab,umur;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas18.csv;
select 
URUT
||'#'||UMUR||'#'||KBK||'#'||PKBK||'#'||KSK||'#'||PKSK||'#'||KCH||'#'||PKCH||'#'||KCM||'#'||PKCM||'#'||JDUK||'#'||PJDUK
from &1..xkualitas18
order by UMUR;

spool off;
------------------------------------------------------------------------------------------------
--Tabel 19. Proporsi Kepala Keluarga Menurut Kelompok Umur, Status Perkawinan dan Jenis Kelamin
------------------------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas19 NOLOGGING TABLESPACE SIAKDATA AS
select 
    row_number() over(partition by no_kab order by no_kab) as URUT
   ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
    ,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75') as UMUR    
    ,sum (case when stat_kwn in(1) and jenis_klmin in(1) then 1 else 0 end) KBKL
    ,sum (case when stat_kwn in(1) and jenis_klmin in(2) then 1 else 0 end) KBKP    
    ,round(count(decode(stat_kwn,1,1))*100/ (select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKBK
    ,sum (case when stat_kwn in(2) and jenis_klmin in(1) then 1 else 0 end) KSKL
    ,sum (case when stat_kwn in(2) and jenis_klmin in(2) then 1 else 0 end) KSKP    
    ,round(count(decode(stat_kwn,2,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKSK
    ,sum (case when stat_kwn in(3) and jenis_klmin in(1) then 1 else 0 end) KCHL
    ,sum (case when stat_kwn in(3) and jenis_klmin in(2) then 1 else 0 end) KCHP    
    ,round(count(decode(stat_kwn,3,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKCH
    ,sum (case when stat_kwn in(4) and jenis_klmin in(1) then 1 else 0 end) KCML
    ,sum (case when stat_kwn in(4) and jenis_klmin in(2) then 1 else 0 end) KCMP    
    ,round(count(decode(stat_kwn,4,1))*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PKCM
    ,count (NIK) as JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni  where stat_hbkel=1 and flag_status='0'),2) PJDUK
FROM biodata_wni WHERE tgl_lhr < '31-DEC-2021' and flag_status='0'
and stat_hbkel=1 and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=15
group by 
grouping sets((no_prop,no_kab
      ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',8, '40-44',9, '45-49'
    ,10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','>= 75'))
,(no_prop,no_kab)
)   
order by no_prop,no_kab,umur ;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas19.csv;
select 
URUT
||'#'||UMUR||'#'||KBKL||'#'||KBKP||'#'||PKBK||'#'||KSKL||'#'||KSKP||'#'||PKSK||'#'||KCHL||'#'||KCHP||'#'||PKCH||'#'||KCML||'#'||KCMP||'#'||PKCM||'#'||JDUK||'#'||PJDUK
from &1..xkualitas19
order by URUT;

spool off;
---------------------------------------------------------------------------------------
--Tabel 20. Kepala Keluarga Menurut Kecamatan, Pendidikan ditammatkan dan Jenis Kelamin
---------------------------------------------------------------------------------------
CREATE TABLE &1..xkualitas20 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
   a.pddk_akh NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(
     select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(
     select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(
     select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b 
    where 
            a.pddk_akh=b.no and 
            a.flag_status='0' and
            a.stat_hbkel=1 and
			b.sect='101'
GROUP BY 
grouping sets((a.pddk_akh,b.descrip)
,()
)
ORDER BY a.pddk_akh;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas20.csv;
select 
NPD
||'#'||NMPD||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkualitas20
order by NPD;

spool off;
--------------------------------------------------------------
--Tabel 21. Penduduk Menurut Jenis Pekerjaan dan Jenis Kelamin
--------------------------------------------------------------
CREATE TABLE &1..xkualitas21 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
   a.jenis_pkrjn NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(
     select count(NIK) from biodata_wni where flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(
     select count(NIK) from biodata_wni where flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(
     select count(NIK) from biodata_wni where flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b 
    where 
            a.jenis_pkrjn=b.no and 
            a.flag_status='0' and 
			b.sect='201'
GROUP BY 
grouping sets((a.jenis_pkrjn,b.descrip)
,()
)
ORDER BY a.jenis_pkrjn;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas21.csv;
select 
NPD
||'#'||NMPD||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkualitas21
order by NPD;

spool off;
---------------------------------------------------------------------
--Tabel 22. Kepala Keluarga Menurut Jenis Pekerjaan dan Jenis Kelamin
---------------------------------------------------------------------
CREATE TABLE &1..xkualitas22 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    a.jenis_pkrjn NPD,b.descrip NMPD
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LK
    ,round(count(decode(jenis_klmin,1,1))*100/(
	 select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PLK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PR
    ,round(count(decode(jenis_klmin,2,1))*100/(
	 select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2)PPR
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(
	 select count(NIK) from biodata_wni where stat_hbkel=1 and flag_status='0'),2) PDUK
FROM biodata_wni a, ref_siak_wni b 
    where 
            a.jenis_pkrjn=b.no and 
            a.flag_status='0' and
			a.Stat_hbkel=1 and
			b.sect='201'
GROUP BY 
grouping sets((a.jenis_pkrjn,b.descrip)
,()
)
ORDER BY a.jenis_pkrjn;

set termout off;
spool \..\..\PROFIL\03_REPORT\xkualitas22.csv;
select 
NPD
||'#'||NMPD||'#'||LK||'#'||PLK||'#'||PR||'#'||PPR||'#'||JDUK||'#'||PDUK
from &1..xkualitas22
order by NPD;

spool off;
----------------------------------------------------------------------------------
--Tabel 23. Jumlah dan Persentasi Kepemilikan Kartu Keluarga Berdasarkan Kecamatan
----------------------------------------------------------------------------------
CREATE TABLE &1..xmilik23 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(b.no_prop,'00'))||''||trim(to_char(b.no_kab,'00'))||''||trim(to_char(b.no_kec,'00'))NOKEC
    ,getnamakec(b.no_kec,b.no_kab,b.no_prop)NMKEC
    ,sum (case when a.pflag='Y' then 1 else 0 end) SCKK
    ,round(count(decode(a.pflag,'Y',1))*100/(
     select count(NO_KK) from biodata_wni where flag_status='0' and stat_hbkel=1),2)PSCKK
    ,sum (case when a.pflag='N' then 1 else 0 end) BCKK
    ,round(count(decode(a.pflag,'N',1))*100/(
     select count(NO_KK) from biodata_wni where flag_status='0' and stat_hbkel=1),2)PBCKK
    ,sum (case when a.NO_KK IS NOT NULL then 1 else 0 end) JDUK
    ,round(count(a.NO_KK)*100/(select count(no_kk) from biodata_wni where flag_status='0' and stat_hbkel=1),2)PDUK
FROM data_keluarga a 
     inner join biodata_wni b on a.no_kk=b.no_kk 
        and b.flag_status='0' 
        and b.stat_hbkel=1
GROUP BY
GROUPING SETS ((b.NO_KEC,b.NO_KAB,b.NO_PROP)
,(b.NO_KAB,b.NO_PROP)
)
ORDER BY b.no_prop,b.NO_KAB,b.NO_KEC;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik23.csv;
select 
NOKEC
||'#'||NMKEC||'#'||SCKK||'#'||PSCKK||'#'||BCKK||'#'||PBCKK||'#'||JDUK||'#'||PDUK
from &1..xmilik23
order by NOKEC;

spool off;
----------------------------------------------------------------------------------
---Tabel 24. Jumlah dan Persentase Kpemilikan Kartu Keluarga Berdasarkan Kecamatan
----------------------------------------------------------------------------------
CREATE TABLE &1..xmilik24 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(a.no_prop,'00'))||''||trim(to_char(a.no_kab,'00'))||''||trim(to_char(a.no_kec,'00'))NOKEC
    ,getnamakec(a.no_kec,a.no_kab,a.no_prop)NMKEC
    ,sum (case when b.pflag in('Y') and a.jenis_klmin in(1) then 1 else 0 end) SCKKL
    ,round(count(decode(b.pflag,'Y',decode(jenis_klmin,1,1)))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1),2)
                PSCKKL
    ,sum (case when b.pflag in('Y') and a.jenis_klmin in(2) then 1 else 0 end) SCKKP
    ,round(count(decode(b.pflag,'Y',decode(jenis_klmin,2,1)))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1),2)PSCKKP
    ,sum (case when b.pflag in('Y') then 1 else 0 end) JSCKK
    ,round(count(decode(b.pflag,'Y',1))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1),2) PJSCKK
    ,sum (case when b.pflag in('N') and a.jenis_klmin in(1) then 1 else 0 end) BCKKL
    ,round(count(decode(b.pflag,'N',decode(jenis_klmin,1,1)))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1 and flag_status='0'),2)PBCKKL
    ,sum (case when b.pflag in('N') and a.jenis_klmin in(2) then 1 else 0 end) BCKKP
    ,round(count(decode(b.pflag,'N',decode(jenis_klmin,2,1)))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1 and flag_status='0'),2)PBCKKP
    ,sum (case when b.pflag in('N') then 1 else 0 end) JBCKK
    ,round(count(decode(b.pflag,'N',1))*100/(
     select count(a.no_kk) from biodata_wni a, data_keluarga b 
             where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1 and flag_status='0'),2)PJBCKK
    ,sum (case when b.no_kk is not null then 1 else 0 end) JKKDUK
    ,round(count(*)*100/(select count(a.no_kk) from biodata_wni a, data_keluarga b 
            where 1=1 and 
                a.no_kk=b.no_kk and 
                stat_hbkel=1 and flag_status='0'),2) PJKKDUK
FROM biodata_wni a, data_keluarga b
WHERE 1=1 and a.no_kk=b.no_kk and stat_hbkel=1 and flag_status='0'
GROUP BY 
GROUPING SETS ((a.no_kec,a.no_kab,a.no_prop)
,(a.no_kab,a.no_prop)
)
ORDER BY a.no_prop,a.no_kab,A.NO_KEC;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik24.csv;
select 
NOKEC
||'#'||NMKEC||'#'||SCKKL||'#'||PSCKKL||'#'||SCKKP||'#'||PSCKKP||'#'||JSCKK||'#'||PJSCKK||'#'||BCKKL||'#'||PBCKKL||'#'||BCKKP||'#'||PBCKKP||'#'||JBCKK||'#'||PJBCKK||'#'||JKKDUK||'#'||PJKKDUK
from &1..xmilik24
order by NOKEC;

spool off;
-------------------------------------------------------------------------
---Tabel 25. Jumlah dan Persentase Kepemilikan KTP Berdasarkan Kecamatan
-------------------------------------------------------------------------
CREATE TABLE &1..xmilik25 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
      trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
     ,getnamakec(no_kec,no_kab,no_prop)NMKEC
     ,sum (case when tgl_cetak_ktp is not null and jenis_klmin=1 then 1 else 0 end) SCKTPL
     ,round(sum (case when tgl_cetak_ktp is not null and jenis_klmin=1 then 1 else 0 end)*100/(
      select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PSCKTPL
     ,sum (case when tgl_cetak_ktp is not null and jenis_klmin=2 then 1 else 0 end) SCKTPP
     ,round(sum (case when tgl_cetak_ktp is not null and jenis_klmin=2 then 1 else 0 end)*100/(
      select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PSCKTPP
     ,sum (case when tgl_cetak_ktp is null and jenis_klmin=1 then 1 else 0 end) BCKTPL
     ,round(sum (case when  tgl_cetak_ktp is null and jenis_klmin=1 then 1 else 0 end)*100/(
      select count(nik) from biodata_wni where (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PBCKTPL
     ,sum (case when tgl_cetak_ktp is null and jenis_klmin=2 then 1 else 0 end) BCKTPP
     ,round(sum (case when tgl_cetak_ktp is null and jenis_klmin=2 then 1 else 0 end)*100/(
       select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PBCKTPP
     ,count(decode(jenis_klmin,1,1)) JWKTPL
     ,round(count(decode(jenis_klmin,1,1))*100/(
       select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PJWKTPL
     ,count(decode(jenis_klmin,2,1)) JWKTPP
     ,round(count(decode(jenis_klmin,2,1))*100/(
      select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PJWKTPP
     ,count(nik) JWKTP
     ,round(count(nik)*100/(select count(nik) from biodata_wni where flag_status='0' and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PJWKTP
FROM biodata_wni
WHERE flag_status='0'
            and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik25.csv;
select 
NOKEC
||'#'||NMKEC||'#'||SCKTPL||'#'||PSCKTPL||'#'||SCKTPP||'#'||PSCKTPP||'#'||BCKTPL||'#'||PBCKTPL||'#'||BCKTPP||'#'||PBCKTPP||'#'||JWKTPL||'#'||PJWKTPL||'#'||JWKTPP||'#'||PJWKTPP||'#'||JWKTP||'#'||PJWKTP
from &1..xmilik25
order by NOKEC;

spool off;
----------------------------------------------------------------------------
--Tabel 25a. Jumlah dan Persentase Kepemilikan KTP-EL Berdasarkan Kecamatan
----------------------------------------------------------------------------
CREATE TABLE &1..xmilik25a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
      trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
     ,getnamakec(no_kec,no_kab,no_prop)NMKEC
     ,sum (case when ektp_current_status_code is not null and jenis_klmin=1 then 1 else 0 end) SCKTPL
     ,sum (case when ektp_current_status_code is not null and jenis_klmin=2 then 1 else 0 end) SCKTPP
     ,round(sum (case when ektp_current_status_code is not null then 1 else 0 end)*100/
      (select count(nik) from biodata_wni where flag_status='0' and 
	  (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PSCKTPP
     ,sum (case when ektp_current_status_code is null and jenis_klmin=1 then 1 else 0 end) BCKTPL
     ,sum (case when ektp_current_status_code is null and jenis_klmin=2 then 1 else 0 end) BCKTPP
     ,round(sum (case when ektp_current_status_code is null then 1 else 0 end)*100/
	  (select count(nik) from biodata_wni where flag_status='0' and 
	  (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PBCKTPP
     ,sum(case when jenis_klmin in(1) then 1 else 0 end) JWKTPL
     ,sum(case when jenis_klmin in(2) then 1 else 0 end) JWKTPP
     ,sum(case when nik is not null then 1 else 0 end) JWKTP
     ,round(sum(case when nik is not null then 1 else 0 end)*100/
	  (select count(nik) from biodata_wni where flag_status='0' and 
	  (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)),2) PJWKTP
FROM biodata_wni
WHERE flag_status='0'
            and (floor(months_between('31-DEC-2021',TGL_LHR)/12)>=17 OR stat_kwn>1)
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik25a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||SCKTPL||'#'||SCKTPP||'#'||PSCKTPP||'#'||BCKTPL||'#'||BCKTPP||'#'||PBCKTPP||'#'||JWKTPL||'#'||JWKTPP||'#'||JWKTP||'#'||PJWKTP
from &1..xmilik25a
order by NOKEC;

spool off;
-----------------------------------------------------
--Tabel 26a. Jumlah Penduduk Menumpang Kartu Keluarga
-----------------------------------------------------
CREATE TABLE &1..xmilik26a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(b.no_prop,'00'))||''||trim(to_char(b.no_kab,'00'))||''||trim(to_char(b.no_kec,'00'))NOKEC
        ,getnamakec(b.no_kec,b.no_kab,b.no_prop)NMKEC
        ,sum (case when a.jenis_klmin in(1) then 1 else 0 end) LNKK
        ,sum (case when a.jenis_klmin in(2) then 1 else 0 end) PNKK
        ,sum (case when a.jenis_klmin in(1,2) then 1 else 0 end) JNKK
        ,round(count(a.nik)*100/(
    select count(a.nik) from biodata_wni a,numpang_kk_detail b where a.nik=b.nik_anggota),2) PJNKK
FROM 
biodata_wni a,numpang_kk_detail b
WHERE
a.nik=b.nik_anggota and to_char(b.created_date,'yyyy')=2017
GROUP BY 
GROUPING SETS((b.no_kec,b.no_kab,b.no_prop)
,(b.no_kab,b.no_prop)
)
ORDER BY b.no_prop,b.no_kab,b.no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik26a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LNKK||'#'||PNKK||'#'||JNKK||'#'||PJNKK
from &1..xmilik26a
order by NOKEC;

spool off;
-----------------------------------------------------------------
--Tabel 26. Jumlah Kepala Keluarga Pisah KK Berdasarkan Kecamatan
-----------------------------------------------------------------
CREATE TABLE &1..xmilik26 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(b.no_prop,'00'))||''||trim(to_char(b.no_kab,'00'))||''||trim(to_char(b.no_kec,'00'))NOKEC
    ,getnamakec(b.no_kec,b.no_kab,b.no_prop) NMKEC
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) LPKK
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) PPKK
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JPKK
    ,round(count(no_kk)*100/(
	 select count(no_kk) from biodata_wni a,pisah_kk b 
	 		where 
				a.no_kk=b.baru_no_kk and 
				a.stat_hbkel=1),2) PJPKK
FROM 
biodata_wni a,pisah_kk b
WHERE
a.no_kk=b.baru_no_kk and to_char(b.created_date,'yyyy')=2017
and a.stat_hbkel=1
GROUP BY 
GROUPING SETS((b.no_kec,b.no_kab,b.no_prop)
,(b.no_kab,b.no_prop)
)
ORDER BY b.no_prop,b.no_kab,b.no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik26.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LPKK||'#'||PPKK||'#'||JPKK||'#'||PJPKK
from &1..xmilik26
order by NOKEC;

spool off;
------------------------------------------------------------------------
--Tabel 27. Jumlah dan Persentase Kepemilikan Akta Berdasarkan Kecamatan
------------------------------------------------------------------------
CREATE TABLE &1..xmilik27 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end) LSAKTA
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end)*100/(
	 select count(NIK) from biodata_wni where flag_status='0'),2) PLSAKTA
    ,sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end) PSAKTA
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end)*100/(
	 select count(NIK) from biodata_wni where flag_status='0'),2) PPSAKTA
    ,sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end) LBAKTA
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end)*100/(
	select count(NIK) from biodata_wni where flag_status='0'),2) PLBAKTA
    ,sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end) PBAKTA
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end)*100/(
	select count(NIK) from biodata_wni where flag_status='0'),2) PPBAKTA
    ,sum (case when jenis_klmin in(1) then 1 else 0 end) JLAKTA
    ,sum (case when jenis_klmin in(2) then 1 else 0 end) JPAKTA
    ,sum (case when jenis_klmin in(1,2) then 1 else 0 end) JDUK
    ,round(count(NIK)*100/(select count(NIK) from biodata_wni where flag_status='0' ),2) PJDUK
FROM biodata_wni where flag_status='0'
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik27.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LSAKTA||'#'||PLSAKTA||'#'||PSAKTA||'#'||PPSAKTA||'#'||LBAKTA||'#'||PLBAKTA||'#'||PBAKTA||'#'||PPBAKTA||'#'||JLAKTA||'#'||JPAKTA||'#'||JDUK||'#'||PJDUK
from &1..xmilik27
order by NOKEC;

spool off;
------------------------------------------------------------------------
--Tabel 27a. Jumlah dan Persentase Kepemilikan Akta Berdasarkan Kecamatan
------------------------------------------------------------------------
CREATE TABLE &1..xmilik27a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00')) NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end) LSAKTA
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end)*100/(
     select count(NIK) from biodata_wni where flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 18),2) PLSAKTA
    ,sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end) PSAKTA
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end)*100/(
     select count(NIK) from biodata_wni where flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 18),2) PPSAKTA
    ,sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end) LBAKTA
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end)*100/(
    select count(NIK) from biodata_wni where flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 18),2) PLBAKTA
    ,sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end) PBAKTA
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end)*100/(
    select count(NIK) from biodata_wni where flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 18),2) PPBAKTA
    ,sum (case when akta_lhr in(2) then 1 else 0 end) JLAKTA
    ,sum (case when akta_lhr in(1) then 1 else 0 end) JPAKTA
    ,round(sum (case when  akta_lhr in(2) then 1 else 0 end) * 100/
    sum (case when akta_lhr in(1,2) then 1 else 0 end),2) JDUK
    ,round(sum (case when  akta_lhr in(1) then 1 else 0 end) * 100/
    sum (case when akta_lhr in(1,2) then 1 else 0 end),2)  PJDUK
FROM biodata_wni where flag_status='0' 
and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 18
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik27a.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LSAKTA||'#'||PLSAKTA||'#'||PSAKTA||'#'||PPSAKTA||'#'||LBAKTA||'#'||PLBAKTA||'#'||PBAKTA||'#'||PPBAKTA||'#'||JLAKTA||'#'||JPAKTA||'#'||JDUK||'#'||PJDUK
from &1..xmilik27a
order by NOKEC;

spool off;
-------------------------------------------------------
---Tabel 28. Kepemilikan Akta berdasarkan Struktur Umur
-------------------------------------------------------
CREATE TABLE &1..xmilik28 NOLOGGING TABLESPACE SIAKDATA AS
select
'1.' as urut
,'0-1' as umur
,sum(case when akta_lhr in(2) and jenis_klmin in(1) then 1 else 0 end) ALAK
,sum(case when akta_lhr in(1) and jenis_klmin in(1) then 1 else 0 end) BLAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(1) then 1 else 0 end) LKAK
,sum(case when akta_lhr in(2) and jenis_klmin in(2) then 1 else 0 end) APAK
,sum(case when akta_lhr in(1) and jenis_klmin in(2) then 1 else 0 end) BPAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(2) then 1 else 0 end) PKAK
,sum(case when jenis_klmin in (1,2) then 1 else 0 end) as JAK
from biodata_wni where flag_status='0' and
floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 1
group by no_kab
union
select
'2.' as urut
,'>1-5' as umur
,sum(case when akta_lhr in(2) and jenis_klmin in(1) then 1 else 0 end) ALAK
,sum(case when akta_lhr in(1) and jenis_klmin in(1) then 1 else 0 end) BLAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(1) then 1 else 0 end) LKAK
,sum(case when akta_lhr in(2) and jenis_klmin in(2) then 1 else 0 end) APAK
,sum(case when akta_lhr in(1) and jenis_klmin in(2) then 1 else 0 end) BPAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(2) then 1 else 0 end) PKAK
,sum(case when jenis_klmin in (1,2) then 1 else 0 end) as JAK
from biodata_wni where flag_status='0' and
floor(months_between('31-DEC-2021',tgl_lhr)/12) between 2 and 5
group by no_kab
union
select
'3.' as urut
,'>5-18' as umur
,sum(case when akta_lhr in(2) and jenis_klmin in(1) then 1 else 0 end) ALAK
,sum(case when akta_lhr in(1) and jenis_klmin in(1) then 1 else 0 end) BLAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(1) then 1 else 0 end) LKAK
,sum(case when akta_lhr in(2) and jenis_klmin in(2) then 1 else 0 end) APAK
,sum(case when akta_lhr in(1) and jenis_klmin in(2) then 1 else 0 end) BPAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(2) then 1 else 0 end) PKAK
,sum(case when jenis_klmin in (1,2) then 1 else 0 end) as JAK
from biodata_wni where flag_status='0' and
floor(months_between('31-DEC-2021',tgl_lhr)/12) between 6 and 18
group by no_prop,no_kab
union
select
'4.' as urut
,'>18+' as umur
,sum(case when akta_lhr in(2) and jenis_klmin in(1) then 1 else 0 end) ALAK
,sum(case when akta_lhr in(1) and jenis_klmin in(1) then 1 else 0 end) BLAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(1) then 1 else 0 end) LKAK
,sum(case when akta_lhr in(2) and jenis_klmin in(2) then 1 else 0 end) APAK
,sum(case when akta_lhr in(1) and jenis_klmin in(2) then 1 else 0 end) BPAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(2) then 1 else 0 end) PKAK
,sum(case when jenis_klmin in (1,2) then 1 else 0 end) as JAK
from biodata_wni where flag_status='0' and
floor(months_between('31-DEC-2021',tgl_lhr)/12) >=19
group by no_prop,no_kab
union
select
'5.' as urut
,'TOTAL' as umur
,sum(case when akta_lhr in(2) and jenis_klmin in(1) then 1 else 0 end) ALAK
,sum(case when akta_lhr in(1) and jenis_klmin in(1) then 1 else 0 end) BLAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(1) then 1 else 0 end) LKAK
,sum(case when akta_lhr in(2) and jenis_klmin in(2) then 1 else 0 end) APAK
,sum(case when akta_lhr in(1) and jenis_klmin in(2) then 1 else 0 end) BPAK
,sum(case when akta_lhr in(1,2) and jenis_klmin in(2) then 1 else 0 end) PKAK
,sum(case when jenis_klmin in (1,2) then 1 else 0 end) as JAK
from biodata_wni where flag_status='0' and
floor(months_between('31-DEC-2021',tgl_lhr)/12) >=0
group by no_prop,no_kab;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik28.csv;
select 
URUT
||'#'||UMUR||'#'||ALAK||'#'||BLAK||'#'||LKAK||'#'||APAK||'#'||BPAK||'#'||PKAK||'#'||JAK
from &1..xmilik28
order by URUT;

spool off;
-------------------------------------------------------------------------------
--Tabel 29. Kepemilikan Akta Kelahiran Kelompok Umur Berdasarkan Jenis Kelamin
-------------------------------------------------------------------------------
CREATE TABLE &1..xmilik29 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
    row_number() over(partition by no_kab order by no_kab) as URUT
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
    ,sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end) LSAK
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=1 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PLSAK
    ,sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end) PSAK
    ,round(sum (case when akta_lhr in(2) and jenis_klmin=2 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PPSAK
    ,sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end) LBAK
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=1 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PLBAK
    ,sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end) PBAK
    ,round(sum (case when akta_lhr in(1) and jenis_klmin=2 then 1 else 0 end)*100/(select count(nik) from biodata_wni where flag_status='0'),2) PPBAK
    ,count(decode(jenis_klmin,1,1)) JLK
    ,count(decode(jenis_klmin,2,1)) JPR
    ,count(nik) JDUK
    ,round(count(*)*100/(select count(nik) from biodata_wni where flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0),2) PJDUK
FROM biodata_wni
WHERE tgl_lhr <= '31-DEC-2021' and flag_status='0' and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=0
GROUP BY 
GROUPING SETS((
    no_kab,no_prop,
     decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60)),
    0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+'))
,(no_kab)
)    
ORDER BY no_kab,UMUR;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik29.csv;
select 
URUT
||'#'||UMUR||'#'||LSAK||'#'||PLSAK||'#'||PSAK||'#'||PPSAK||'#'||LBAK||'#'||PLBAK||'#'||PBAK||'#'||PPBAK||'#'||JLK||'#'||JPR||'#'||JDUK||'#'||PJDUK
from &1..xmilik29
order by URUT;

spool off;
---------------------------------------------------------------
---Tabel 30. Kepemilikan Akta Perkawinan Berdasarkan Kecamatan
---------------------------------------------------------------
CREATE TABLE &1..xmilik30 NOLOGGING TABLESPACE SIAKDATA AS
SELECT  
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
    ,getnamakec(no_kec,no_kab,no_prop)NMKEC
    ,sum (case when akta_kwn in(2) and jenis_klmin=1 then 1 else 0 end) LSAK
    ,round(sum (case when akta_kwn in(2) and jenis_klmin=1 then 1 else 0 end)*100/(select count(nik) from biodata_wni where stat_kwn=2),2)PLSAK
    ,sum (case when akta_kwn in(2) and jenis_klmin=2 then 1 else 0 end)PSAK
    ,round(sum (case when akta_kwn in(2) and jenis_klmin=2 then 1 else 0 end)*100/(select count(nik) from biodata_wni where stat_kwn=2),2)PPSAK
    ,sum (case when akta_kwn in(1) and jenis_klmin=1 then 1 else 0 end) LBAK
    ,round(sum (case when akta_kwn in(1) and jenis_klmin=1 then 1 else 0 end)*100/(select count(nik) from biodata_wni where stat_kwn=2),2) PLBAK
    ,sum (case when akta_kwn in(1) and jenis_klmin=2 then 1 else 0 end) PBAK
    ,round(sum (case when akta_kwn in(1) and jenis_klmin=2 then 1 else 0 end)*100/(select count(nik) from biodata_wni where stat_kwn=2),2) PPBAK
    ,count(decode(jenis_klmin,1,1)) JLK
    ,count(decode(jenis_klmin,2,1)) JPR
    ,count(nik) JDUK
    ,round(count(nik)*100/(select count(nik) from biodata_wni where stat_kwn=2 and flag_status='0'),2) PJDUK
FROM biodata_wni
WHERE stat_kwn=2 and flag_status='0'
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,no_kab)
)
ORDER BY no_prop,no_kab,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik30.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LSAK||'#'||PLSAK||'#'||PSAK||'#'||PPSAK||'#'||LBAK||'#'||PLBAK||'#'||PBAK||'#'||PPBAK||'#'||JLK||'#'||JPR||'#'||JDUK||'#'||PJDUK
from &1..xmilik30
order by NOKEC;

spool off;
-------------------------------------------------------------------------------
--Tabel 31. Kepemilikan Akta Perkawinan Kelompok Umur Berdasarkan Jenis Kelamin
-------------------------------------------------------------------------------
CREATE TABLE &1..xmilik31 NOLOGGING TABLESPACE SIAKDATA AS
SELECT 
row_number() over(partition by no_kab order by no_kab) as URUT
    ,decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60))
    ,0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+')  UMUR
    ,sum (case when akta_kwn in(2) and jenis_klmin=1 then 1 else 0 end) LSAK
    ,round(sum (case when akta_kwn in(2) and jenis_klmin=1 then 1 else 0 end)*100/(
     select count(nik) from biodata_wni where stat_kwn=2),2) PLSAK
    ,sum (case when akta_kwn in(2) and jenis_klmin=2 then 1 else 0 end) PSAK
    ,round(sum (case when akta_kwn in(2) and jenis_klmin=2 then 1 else 0 end)*100/(
    select count(nik) from biodata_wni where stat_kwn=2),2) PPSAK
    ,sum (case when akta_kwn in(1) and jenis_klmin=1 then 1 else 0 end) LBAK
    ,round(sum (case when akta_kwn in(1) and jenis_klmin=1 then 1 else 0 end)*100/(
    select count(nik) from biodata_wni where stat_kwn=2),2) PLBAK
    ,sum (case when akta_kwn in(1) and jenis_klmin=2 then 1 else 0 end) PBAK
    ,round(sum (case when akta_kwn in(1) and jenis_klmin=2 then 1 else 0 end)*100/(
    select count(nik) from biodata_wni where stat_kwn=2),2) PPBAK
    ,count(decode(jenis_klmin,1,1)) JLK
    ,count(decode(jenis_klmin,2,1)) JPR
    ,count(nik) JDUK
    ,round(count(nik)*100/(select count(nik) from biodata_wni where stat_kwn=2 and flag_status='0'),2) PJDUK
FROM biodata_wni
WHERE tgl_lhr < '31-DEC-2021' and flag_status='0' and stat_kwn=2
GROUP BY 
GROUPING SETS((
    no_kab,no_prop,
     decode (floor ((months_between ('31-DEC-2021', tgl_lhr) / 60)),
    0, '00-04',1, '05-09',2, '10-14',3, '15-19',4, '20-24',5, '25-29',6, '30-34',7, '35-39',
    8, '40-44',9, '45-49',10, '50-54',11, '55-59',12, '60-64',13, '65-69',14, '70-74','75>+'))
,(NO_KAB)
)    
ORDER BY no_kab,UMUR;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik31.csv;
select 
URUT
||'#'||UMUR||'#'||LSAK||'#'||PLSAK||'#'||PSAK||'#'||PPSAK||'#'||LBAK||'#'||PLBAK||'#'||PBAK||'#'||PPBAK||'#'||JLK||'#'||JPR||'#'||JDUK||'#'||PJDUK
from &1..xmilik31
order by URUT;

spool off;
--------------------------------------------------------------
---Tabel 32. Kepemilikan Akta Perceraian Berdasarkan Kecamatan
--------------------------------------------------------------
CREATE TABLE &1..xmilik32 NOLOGGING TABLESPACE SIAKDATA AS
SELECT
    trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00'))||''||trim(to_char(no_kec,'00'))NOKEC
    ,getnamakec(no_kec,no_kab,no_prop) NMKEC
    ,sum (case when no_akta_crai is not null and jenis_klmin=1 then 1 else 0 end) LSAK
    ,round(sum (case when no_akta_crai is not null and jenis_klmin=1 then 1 else 0 end)*100/(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0'),2)PLSAK
    ,sum (case when no_akta_crai is not null and jenis_klmin=2 then 1 else 0 end) PSAK
    ,round(sum (case when no_akta_crai is not null and jenis_klmin=2 then 1 else 0 end)*100/(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0'),2) PPSAK
    ,sum (case when no_akta_crai is null and jenis_klmin=1 then 1 else 0 end) LBAK
    ,round(sum (case when no_akta_crai is null and jenis_klmin=1 then 1 else 0 end)*100/(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0'),2) PLBAK
    ,sum (case when no_akta_crai is null and jenis_klmin=2 then 1 else 0 end) PBAK
    ,round(sum (case when no_akta_crai is null and jenis_klmin=2 then 1 else 0 end)*100/(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0'),2) PPBAK
    ,count(decode(jenis_klmin,1,1)) JLK
    ,count(decode(jenis_klmin,2,1)) JPR
    ,count(*) JDUK
    ,round(count(*)*100/(select count(*) from biodata_wni where stat_kwn=3 and flag_status='0'),2) PJDUK
FROM siakoff.biodata_wni 
WHERE stat_kwn=3 and flag_status='0'
GROUP BY 
GROUPING SETS((no_prop,no_kab,no_kec)
,(no_prop,NO_KAB)
)
ORDER BY no_prop,NO_KAB,no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmilik32.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LSAK||'#'||PLSAK||'#'||PSAK||'#'||PPSAK||'#'||LBAK||'#'||PLBAK||'#'||PBAK||'#'||PPBAK||'#'||JLK||'#'||JPR||'#'||JDUK||'#'||PJDUK
from &1..xmilik32
order by NOKEC;

spool off;
---------------------------------------------------------------------------------
--Tabel 33. Rekapitulasi Migrasi Pindah Antar Propinsi Berdasarkan Kecamatan Asal
---------------------------------------------------------------------------------
CREATE TABLE &1..xmigrasik533 NOLOGGING TABLESPACE SIAKDATA AS
select
		trim(to_char(a.from_no_prop,'00'))||''||trim(to_char(a.from_no_kab,'00')) as KODE
		,getnamakec(a.from_no_kec,a.from_no_kab,a.from_no_prop) as NMKEC
		,sum(case when b.jenis_klmin in(1) then 1 else null end) as LPROP
		,sum(case when b.jenis_klmin in(2) then 1 else null end) as PPROP
		,sum(case when b.jenis_klmin in(1,2) then 1 else null end) as JPROP
	from pindah_header a join pindah_detail b on
	a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(5) where 
	a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
	a.from_no_kab in(select no_kab from biodata_wni where flag_status='0') 
	and to_char(a.created_date,'yyyy')=2017 
		group by
		grouping sets ((a.from_no_kec,a.from_no_prop,a.from_no_kab)
		,(a.from_no_prop,a.from_no_kab)
		)
		order by kode,a.from_no_prop,a.from_no_kab,a.from_no_kec;
		
set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasik533.csv;
select 
KODE
||'#'||NMKEC||'#'||LPROP||'#'||PPROP||'#'||JPROP
from &1..xmigrasik533
order by KODE;

spool off;
---------------------------------------------------------------------------------------
--Tabel 34. Rekapitulasi Migrasi Pindah Antar Kabupaten/Kota Berdasarkan Kecamatan Asal
---------------------------------------------------------------------------------------
CREATE TABLE &1..xmigrasik434 NOLOGGING TABLESPACE SIAKDATA AS
select
		trim(to_char(a.from_no_prop,'00'))||''||trim(to_char(a.from_no_kab,'00')) as KODE
		,getnamakec(a.from_no_kec,a.from_no_kab,a.from_no_prop) as NMKEC
		,sum(case when b.jenis_klmin in(1) then 1 else null end) as LPROP
		,sum(case when b.jenis_klmin in(2) then 1 else null end) as PPROP
		,sum(case when b.jenis_klmin in(1,2) then 1 else null end) as JPROP
	from pindah_header a join pindah_detail b on
	a.no_pindah=b.no_pindah and a.klasifikasi_pindah in(4) where 
	a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and
	a.from_no_kab in(select no_kab from biodata_wni where flag_status='0')  
	and to_char(a.created_date,'yyyy')=2017 
		group by
		grouping sets ((a.from_no_kec,a.from_no_prop,a.from_no_kab)
		,(a.from_no_prop,a.from_no_kab)
		)
		order by kode,a.from_no_prop,a.from_no_kab,a.from_no_kec;
		
set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasik434.csv;
select 
KODE
||'#'||NMKEC||'#'||LPROP||'#'||PPROP||'#'||JPROP
from &1..xmigrasik434
order by KODE;

spool off;
-----------------------------------------------------------------------------------		
--Tabel 35. Rekapitulasi Migrasi Masuk Antar Propinsi Berdasarkan Kecamatan Tujuan
-----------------------------------------------------------------------------------		
CREATE TABLE &1..xmigrasiM535 NOLOGGING TABLESPACE SIAKDATA AS
select
	trim(to_char(a.no_prop,'00'))||''||trim(to_char(a.no_kab,'00')) as KODE
	,getnamakec(a.no_kec,a.no_kab,a.no_prop) as NMKEC
	,sum(case when c.jenis_klmin in(1)   then 1 else null end) as LPROP
	,sum(case when c.jenis_klmin in(2) then 1 else null end) as PPROP
	,sum(case when c.jenis_klmin in(1,2)  then 1 else null end) as JPROP
from datang_header a
	join datang_detail b on a.no_datang=b.no_datang
	join pindah_detail c on b.nik=c.nik and a.klasifikasi_pindah in(5) where 
	a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
	a.no_kab in(select no_kab from biodata_wni where flag_status='0')  
	and to_char(a.created_date,'yyyy')=2017 
group by
grouping sets((a.no_prop,a.no_kab,a.no_kec)
,(a.no_prop,b.no_kab)
)
order by a.no_prop,a.no_kab,a.no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasiM535.csv;
select 
KODE
||'#'||NMKEC||'#'||LPROP||'#'||PPROP||'#'||JPROP
from &1..xmigrasiM535
order by KODE;

spool off;
----------------------------------------------------------------------------------------	
--Tabel 36. Rekapitulasi Migrasi Masuk Antar Kabupaten/Kota Berdasarkan Kecamatan Tujuan
----------------------------------------------------------------------------------------	
CREATE TABLE &1..xmigrasiM436 NOLOGGING TABLESPACE SIAKDATA AS
select
	trim(to_char(a.no_prop,'00'))||''||trim(to_char(a.no_kab,'00')) as KODE
	,getnamakec(a.no_kec,a.no_kab,a.no_prop) as NMKEC
	,sum(case when c.jenis_klmin in(1)   then 1 else null end) as LPROP
	,sum(case when c.jenis_klmin in(2) then 1 else null end) as PPROP
	,sum(case when c.jenis_klmin in(1,2)  then 1 else null end) as JPROP
from datang_header a
	join datang_detail b on a.no_datang=b.no_datang
	join pindah_detail c on b.nik=c.nik and a.klasifikasi_pindah in(4) where 
	a.no_prop in(select no_prop from biodata_wni where flag_status='0') and
	a.no_kab in(select no_kab from biodata_wni where flag_status='0')  
	and to_char(a.created_date,'yyyy')=2017 
group by
grouping sets((a.no_prop,a.no_kab,a.no_kec)
,(a.no_prop,a.no_kab)
)
order by a.no_prop,a.no_kab,a.no_kec;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasiM436.csv;
select 
KODE
||'#'||NMKEC||'#'||LPROP||'#'||PPROP||'#'||JPROP
from &1..xmigrasiM436
order by KODE;

spool off;
-----------------------------------------------------------------	
--GRAPHICS 4a. Pyramida Penduduk, Kelompok Umur dan Jenis Kelamin
-----------------------------------------------------------------	
CREATE TABLE &1..xgraphics04a NOLOGGING TABLESPACE SIAKDATA AS
SELECT
      trim(to_char(no_prop,'00'))||''||trim(to_char(no_kab,'00')) NOKEC
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 4 then -1 else 0 end) L0004
	 ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 0 and 4 then 1 else 0 end) P0004
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 5 and 9 then -1 else 0 end) L0509
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 5 and 9 then 1 else 0 end) P0509
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 10 and 14 then -1 else 0 end) L1014
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 10 and 14 then 1 else 0 end) P1014
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 19 then -1 else 0 end) L1519
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 19 then 1 else 0 end) P1519
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 20 and 24 then -1 else 0 end) L2024
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 20 and 24 then 1 else 0 end) P2024
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 25 and 29 then -1 else 0 end) L2529
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 25 and 29 then 1 else 0 end) P2529
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 30 and 34 then -1 else 0 end) L3034
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 30 and 34 then 1 else 0 end) P3034
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 35 and 39 then -1 else 0 end) L3539
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 35 and 39 then 1 else 0 end) P3539
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 40 and 44 then -1 else 0 end) L4044
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 40 and 44 then 1 else 0 end) P4044
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 45 and 49 then -1 else 0 end) L4549
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 45 and 49 then 1 else 0 end) P4549
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 50 and 54 then -1 else 0 end) L5054
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 50 and 54 then 1 else 0 end) P5054
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 55 and 59 then -1 else 0 end) L5559
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 55 and 59 then 1 else 0 end) P5559
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 60 and 64 then -1 else 0 end) L6064
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 60 and 64 then 1 else 0 end) P6064
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 65 and 69 then -1 else 0 end) L6569
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 65 and 69 then 1 else 0 end) P6569
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 70 and 74 then -1 else 0 end) L7074
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12) between 70 and 74 then 1 else 0 end) P7074
     ,sum (case when jenis_klmin in(1) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=75 then -1 else 0 end) L75UP
     ,sum (case when jenis_klmin in(2) and floor(months_between('31-DEC-2021',tgl_lhr)/12)>=75  then 1 else 0 end) P75UP	 
FROM biodata_wni where flag_status='0'
GROUP BY no_prop,no_kab;

set termout off;
spool \..\..\PROFIL\03_REPORT\xgraphics04a.csv;
select 
NOKEC
||'#'||L0004||'#'||P0004||'#'||L0509||'#'||P0509||'#'||L1014||'#'||P1014||'#'||L1519||'#'||P1519||'#'||L2024||'#'||P2024||'#'||L2529
||'#'||P2529||'#'||L3034||'#'||P3034||'#'||L3539||'#'||P3539||'#'||L4044||'#'||P4044||'#'||L4549||'#'||P4549||'#'||L5054||'#'||P5054
||'#'||L5559||'#'||P5559||'#'||L6064||'#'||P6064||'#'||L6569||'#'||P6569||'#'||L7074||'#'||P7074||'#'||L75UP||'#'||P75UP
from &1..xgraphics04a
order by NOKEC;

spool off;
-----------------------------------------------------------------------------------------
--Graphics 33. Rekapitulasi Penduduk Migrasi Keluar berdasarkan Status Klasifikasi Pindah
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xgraphicsK51 NOLOGGING TABLESPACE SIAKDATA AS
select
a.klasifikasi_pindah as urut
,decode(a.klasifikasi_pindah
    ,1,'DALAM DESA'
    ,2,'ANTAR DESA'
    ,3,'ANTAR KECAMATAN'
    ,4,'ANTAR KABUPATEN'
    ,5,'ANTAR PROPINSI') as MIGRASI_KELUAR
	,sum(case when b.jenis_klmin in(1) then 1 else null end ) LK
	,sum(case when b.jenis_klmin in(2) then 1 else null end ) PR
	,sum(case when b.jenis_klmin in(1,2) then 1 else null end ) JUMLAH
from pindah_header a join pindah_detail b on
a.no_pindah=b.no_pindah  where
a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and 
a.from_no_kab in(select no_kab from biodata_wni where flag_status='0') 
and to_char(a.created_date,'yyyy')=2017
group by a.klasifikasi_pindah
order by a.klasifikasi_pindah;

set termout off;
spool \..\..\PROFIL\03_REPORT\xgraphicsK51.csv;
select 
URUT
||'#'||MIGRASI_KELUAR||'#'||LK||'#'||PR||'#'||JUMLAH
from &1..xgraphicsK51
order by URUT;

spool off;
-----------------------------------------------------------------------------------------
--Graphics 34. Rekapitulasi Penduduk Migrasi Keluar berdasarkan Klasifikasi Pindah 12345
-----------------------------------------------------------------------------------------
CREATE TABLE &1..xmigrasiP12345 NOLOGGING TABLESPACE SIAKDATA AS
select
a.klasifikasi_pindah as urut
,decode(a.klasifikasi_pindah
    ,1,'DALAM DESA'
    ,2,'ANTAR DESA'
    ,3,'ANTAR KECAMATAN'
    ,4,'ANTAR KABUPATEN'
    ,5,'ANTAR PROPINSI') as MIGRASI_KELUAR
    ,sum(case when b.jenis_klmin in(1) then 1 else null end ) LK
    ,sum(case when b.jenis_klmin in(2) then 1 else null end ) PR
    ,sum(case when b.jenis_klmin in(1,2) then 1 else null end ) JUMLAH
from pindah_header a join pindah_detail b on
a.no_pindah=b.no_pindah  where
a.from_no_prop in(select no_prop from biodata_wni where flag_status='0') and 
a.from_no_kab in(select no_kab from biodata_wni where flag_status='0') 
and to_char(a.created_date,'yyyy')=2017
group by 
grouping sets((a.klasifikasi_pindah)
,()
)
order by a.klasifikasi_pindah;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasiP12345.csv;
select 
URUT
||'#'||MIGRASI_KELUAR||'#'||LK||'#'||PR||'#'||JUMLAH
from &1..xmigrasiP12345
order by URUT;

spool off;
------------------------------------------------------------------------------------
--Graphics 35. Rekapitulasi Penduduk Migrasi Masuk Sesuai Status Klasifikasi Datang
------------------------------------------------------------------------------------
CREATE TABLE &1..xgraphicsM51 NOLOGGING TABLESPACE SIAKDATA AS
select
a.klasifikasi_pindah as urut
,decode(a.klasifikasi_pindah
    ,1,'DALAM DESA'
    ,2,'ANTAR DESA'
    ,3,'ANTAR KECAMATAN'
    ,4,'ANTAR KABUPATEN'
    ,5,'ANTAR PROPINSI') as MIGRASI_MASUK
,sum(case when c.jenis_klmin in(1) then 1 else 0 end ) LK
,sum(case when c.jenis_klmin in(2) then 1 else 0 end ) PR
,sum(case when c.jenis_klmin in(1,2) then 1 else 0 end ) JUMLAH
from datang_header a join datang_detail b on a.no_datang=b.no_datang 
join pindah_detail c on b.nik=c.nik  where  
a.no_prop in(select no_prop from biodata_wni where flag_status='0') and 
a.no_kab in(select no_kab from biodata_wni where flag_status='0')
and to_char(a.created_date,'yyyy')=2017
group by a.klasifikasi_pindah
order by a.klasifikasi_pindah;

set termout off;
spool \..\..\PROFIL\03_REPORT\xgraphicsM51.csv;
select 
URUT
||'#'||MIGRASI_MASUK||'#'||LK||'#'||PR||'#'||JUMLAH
from &1..xgraphicsM51
order by URUT;

spool off;
------------------------------------------------------------------------------------
--Graphics 35. Rekapitulasi Penduduk Migrasi Masuk Sesuai Klasifikasi Datang 12345
------------------------------------------------------------------------------------
CREATE TABLE &1..xmigrasiD12345 NOLOGGING TABLESPACE SIAKDATA AS
select
a.klasifikasi_pindah as urut
,decode(a.klasifikasi_pindah
    ,1,'DALAM DESA'
    ,2,'ANTAR DESA'
    ,3,'ANTAR KECAMATAN'
    ,4,'ANTAR KABUPATEN'
    ,5,'ANTAR PROPINSI') as MIGRASI_MASUK
,sum(case when c.jenis_klmin in(1) then 1 else 0 end ) LK
,sum(case when c.jenis_klmin in(2) then 1 else 0 end ) PR
,sum(case when c.jenis_klmin in(1,2) then 1 else 0 end ) JUMLAH
from datang_header a join datang_detail b on a.no_datang=b.no_datang 
join pindah_detail c on a.no_pindah=c.no_pindah and b.nik=c.nik  where  
a.no_prop in(select no_prop from biodata_wni where flag_status='0') and 
a.no_kab in(select no_kab from biodata_wni where flag_status='0')
and to_char(a.created_date,'yyyy')=2017
group by 
grouping sets((a.klasifikasi_pindah)
,()
)
order by a.klasifikasi_pindah;

set termout off;
spool \..\..\PROFIL\03_REPORT\xmigrasiD12345.csv;
select 
URUT
||'#'||MIGRASI_MASUK||'#'||LK||'#'||PR||'#'||JUMLAH
from &1..xmigrasiD12345
order by URUT;

spool off;
--------------------------------------------------------
--GRAPHICS 7. GRAPHICS ANGKATAN KERJA YANG SUDAH BEKERJA
--------------------------------------------------------
CREATE TABLE &1..xgraphicsSK NOLOGGING TABLESPACE SIAKDATA AS
SELECT no_kec NOKEC
        ,getnamakec(no_kec,no_kab,no_prop) NMKEC
        ,sum (case when jenis_klmin in(1) then 1 else null end ) LK
        ,sum (case when jenis_klmin in(2) then 1 else null end) PR
        ,sum (case when jenis_klmin in(1,2) then 1 else null end) JML
      FROM biodata_wni where flag_status='0' and 
	  floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 
	  and jenis_pkrjn>1
      GROUP BY no_kec,no_kab,no_prop
   	  ORDER BY no_kec;
	  
set termout off;
spool \..\..\PROFIL\03_REPORT\xgraphicsSK.csv;
select 
NOKEC
||'#'||NMKEC||'#'||LK||'#'||PR||'#'||JML
from &1..xgraphicsSK
order by NOKEC;

spool off;
--------------------------------------------------------	  
--GRAPHICS 7. GRAPHICS ANGKATAN KERJA YANG BELUM BEKERJA	  
--------------------------------------------------------
CREATE TABLE &1..xgraphicsBK NOLOGGING TABLESPACE SIAKDATA AS
SELECT no_kec NOKEC
        ,getnamakec(no_kec,no_kab,no_prop) NMKEC
        ,sum (case when jenis_klmin in(1) then 1 else null end ) PRIA
        ,sum (case when jenis_klmin in(2) then 1 else null end) WANITA
        ,sum (case when jenis_klmin in(1,2) then 1 else null end) JUMLAH
      FROM biodata_wni where flag_status='0' and 
	  floor(months_between('31-DEC-2021',tgl_lhr)/12) between 15 and 64 and
	  jenis_pkrjn=1
      GROUP BY no_kec,no_kab,no_prop
   	  ORDER BY no_kec;
	  
set termout off;
spool \..\..\PROFIL\03_REPORT\xgraphicsBK.csv;
select 
NOKEC
||'#'||NMKEC||'#'||PRIA||'#'||WANITA||'#'||JUMLAH
from &1..xgraphicsBK
order by NOKEC;

spool off;
--------------------------------------------------------		  

	  
set termout on;
set pagesize 100;
set heading on;
set feedback on;
set linesize80;
exit;

