Script Mengolah DKB tahun 2020 Semester 1

Script Untuk memberikan Pengguna Khusus Hak untuk Select, Insert, Update pada Table-table SIAKOFF

select 
 DENSE_RANK() OVER (ORDER BY np,nk,nc,nl) NO
, GETNAMAPROP(np) as "NAMA PROPINSI"
, GETNAMAKAB(nk,np) AS "NAMA KABUPATEN"
, getnamakec(nc,nk,np) AS "NAMA KECAMATAN"
, GETNAMAKEL(nl,nc,nk,np) as "NAMA DESA/KEL"
, sum(laki)  as "LAKI-LAKI"
, sum(perempuan)  as "PEREMPUAN"
, sum(jduk)  as "JUMLAH PENDUDUK"
, sum(wktp_lk) as "WAJIB KTP LAKI2"
, sum(wktp_pr) as "WAJIB KTP PEREMPUAN"
, sum(wktp) as "TOTAL WAJIB KTP"
, sum(anak_bwh17_lk) as "LAKI-LAKI U17 BELUM WKTP"
, sum(anak_bwh17_pr) as "PEREMPUAN U17 BELUM WKTP"
, sum(anak_bwh17) as "TOTAL U17 BELUM WKTP"
, sum(anak_bwh18_lk) as "LAKI-LAKI U18" 
, sum(anak_bwh18_pr) as "PEREMPUAN U18"
, sum(anak_bwh18) as "TOTAL U18"
, sum(anak_bwh18_akta_lk) as "LAKI-LAKI U18 MEMILIKI AKTA" 
, sum(anak_bwh18_akta_pr) as "PEREMPUAN U18 MEMILIKI AKTA" 
, sum(anak_bwh18_akta) as "TOTAL U18 MEMILIKI AKTA" 
, sum(laki_lahir_thn_ini) as "LAKI-LAKI LAHIR THN INI" 
, sum(perempuan_lahir_thn_ini) as "PEREMPUAN LAHIR THN INI"
, sum(total_lahir_thn_ini) as "TOTAL LAHIR THN INI"
, sum(case when mati_laki > 0 then mati_laki else 0 end) as "PDDK LAKI-LAKI MATI TAHUN INI"
, sum(case when mati_pr > 0 then mati_pr else 0 end) as "PDDK PEREMPUAN MATI TAHUN INI"
, sum(case when total_mati > 0 then total_mati else 0 end) as "TOTAL PDDK MATI TAHUN INI"
, sum(usia05) as "USIA 0-4"
, sum(usia0510) as "USIA 5-10"
, sum(usia1015) as "USIA 10-14"
, sum(usia1520) as "USIA 15-20"
, sum(usia2025) as "USIA 20-24"
, sum(usia2530) as "USIA 25-30"
, sum(usia3035) as "USIA 30-34"
, sum(usia3540) as "USIA 35-40"
, sum(usia4045) as "USIA 40-44"
, sum(usia4550) as "USIA 45-50"
, sum(usia5055) as "USIA 50-54"
, sum(usia5560) as "USIA 55-60"
, sum(usia6065) as "USIA 60-64"
, sum(usia6570) as "USIA 65-70"
, sum(usia7075) as "USIA 70-74"
, sum(usia75up) as "USIA >75"
, sum(JUM_KK) as "JUMLAH KK"
, sum(laki_belum_kawin) as "LAKI-LAKI BELUM KAWIN"
, sum(perempuan_belum_kawin) as "PEREMPUAN BELUM KAWIN"
, sum(total_belum_kawin) as "TOTAL BELUM KAWIN"
, sum(laki_kawin) as "LAKI-LAKI KAWIN"
, sum(perempuan_kawin) as "PEREMPUAN KAWIN"
, sum(total_kawin) as "TOTAL KAWIN"
, sum(laki_cerai_hidup) as "LAKI-LAKI CERAI HIDUP"
, sum(perempuan_cerai_hidup) as "PEREMPUAN CERAI HIDUP"
, sum(total_cerai_hidup) as "TOTAL CERAI HIDUP"
, sum(laki_cerai_mati) as "LAKI-LAKI CERAI MATI"
, sum(perempuan_cerai_mati) as "PEREMPUAN CERAI MATI"
, sum(total_cerai_mati)  as "TOTAL CERAI MATI"
, sum(laki_kawin_trctt)  as "LAKI-LAKI KAWIN TERCATAT"
, sum(perempuan_kawin_trctt)  as "PEREMPUAN KAWIN TERCATAT"
, sum(total_kawin_trctt)  as "TOTAL KAWIN TERCATAT"
, sum(laki_cerai_hidup_trctt)  as "LAKI-LAKI CERAI HIDUP TERCATAT"
, sum(perempuan_cerai_hidup_trctt)   as "PEREMPUAN CERAI HIDUP TERCATAT"
, sum(total_cerai_hidup_trctt)  as "TOTAL CERAI HIDUP TERCATAT"
, sum(laki_cerai_mati_trctt)  as "LAKI-LAKI CERAI MATI TERCATAT"
, sum(perempuan_cerai_mati_trctt)  as "PEREMPUAN CERAI MATI TERCATAT"
, sum(total_cerai_mati_trctt) as "TOTAL CERAI MATI TERCATAT"
, sum(islam) as "ISLAM"
, sum(kristen) as "KRISTEN"
, sum(khatolik) as "KHATOLIK"
, sum(hindu) as "HINDU"
, sum(budha) as "BUDHA"
, sum(KHONGHUCU) as "KHONGHUCU"
, sum(kepercayaan) as "KEPERCAYAAN"
, sum(tbsklh) as "TIDAK/BELUM SEKOLAH"
, sum(btsd) as "BELUM TAMAT SD SEDERAJAT"
, sum(sd) as "TAMAT SD SEDERAJAT"
, sum(smp) as " SLTP SEDERAJAT"
, sum(sma) as " SLTA SEDERAJAT"
, sum(d1d2) as " DIPLOMA I/II SEDERAJAT"
, sum(d3) as "AKDM/DIPLOMAIII/SARJANA MUDA"
, sum(d4s1) as "DIPLOMA IV/ STRATA I"
, sum(s2) as "STRATA-II"
, sum(s3) as "STRATA-III"
, sum(p1) as "BELUM/TIDAK BEKERJA"
, sum(p2) as "MENGURUS RUMAH TANGGA"
, sum(p3) as "PELAJAR/MAHASISWA"
, sum(p4) as "PENSIUNAN"
, sum(p5) as "PEGAWAI NEGERI SIPIL"
, sum(p6) as "TENTARA NASIONAL INDONESIA"
, sum(p7) as "KEPOLISIAN RI"
, sum(p8) as "PERDAGANGAN"
, sum(p9) as "PETANI/PEKEBUN"
, sum(p10) as "PETERNAK"
, sum(p11) as "NELAYAN/PERIKANAN"
, sum(p12) as "INDUSTRI"
, sum(p13) as "KONSTRUKSI"
, sum(p14) as "TRANSPORTASI"
, sum(p15) as "KARYAWAN SWASTA"
, sum(p16) as "KARYAWAN BUMN"
, sum(p17) as "KARYAWAN BUMD"
, sum(p18) as "KARYAWAN HONORER"
, sum(p19) as "BURUH HARIAN LEPAS"
, sum(p20) as "BURUH TANI/PERKEBUNAN"
, sum(p21) as "BURUH NELAYAN/PERIKANAN"
, sum(p22) as "BURUH PETERNAKAN"
, sum(p23) as "PEMBANTU RUMAH TANGGA"
, sum(p24) as "TUKANG CUKUR"
, sum(p25) as "TUKANG LISTRIK"
, sum(p26) as "TUKANG BATU"
, sum(p27) as "TUKANG KAYU"
, sum(p28) as "TUKANG SOL SEPATU"
, sum(p29) as "TUKANG LAS/PANDAI BESI"
, sum(p30) as "TUKANG JAHIT"
, sum(p31) as "TUKANG GIGI"
, sum(p32) as "PENATA RIAS"
, sum(p33) as "PENATA BUSANA"
, sum(p34) as "PENATA RAMBUT"
, sum(p35) as "MEKANIK"
, sum(p36) as "SENIMAN"
, sum(p37) as "TABIB"
, sum(p38) as "PARAJI"
, sum(p39) as "PERANCANG BUSANA"
, sum(p40) as "PENTERJEMAH"
, sum(p41) as "IMAM MESJID"
, sum(p42) as "PENDETA"
, sum(p43) as "PASTOR"
, sum(p44) as "WARTAWAN"
, sum(p45) as "USTADZ/MUBALIGH"
, sum(p46) as "JURU MASAK"
, sum(p47) as "PROMOTOR ACARA"
, sum(p48) as "ANGGOTA DPR-RI"
, sum(p49) as "ANGGOTA DPD"
, sum(p50) as "ANGGOTA BPK"
, sum(p51) as "PRESIDEN"
, sum(p52) as "WAKIL PRESIDEN"
, sum(p53) as "ANGGOTA MAHKAMAH KONSTITUSI"
, sum(p54) as "ANGGOTA KABINET/KEMENTERIAN"
, sum(p55) as "DUTA BESAR"
, sum(p56) as "GUBERNUR"
, sum(p57) as "WAKIL GUBERNUR"
, sum(p58) as "BUPATI"
, sum(p59) as "WAKIL BUPATI"
, sum(p60) as "WALIKOTA"
, sum(p61) as "WAKIL WALIKOTA"
, sum(p62) as "ANGGOTA DPRD PROVINSI"
, sum(p63) as "ANGGOTA DPRD KABUPATEN/KOTA"
, sum(p64) as "DOSEN"
, sum(p65) as "GURU"
, sum(p66) as "PILOT"
, sum(p67) as "PENGACARA"
, sum(p68) as "NOTARIS"
, sum(p69) as "ARSITEK"
, sum(p70) as "AKUNTAN"
, sum(p71) as "KONSULTAN"
, sum(p72) as "DOKTER"
, sum(p73) as "BIDAN"
, sum(p74) as "PERAWAT"
, sum(p75) as "APOTEKER"
, sum(p76) as "PSIKIATER/PSIKOLOG"
, sum(p77) as "PENYIAR TELEVISI"
, sum(p78) as "PENYIAR RADIO"
, sum(p79) as "PELAUT"
, sum(p80) as "PENELITI"
, sum(p81) as "SOPIR"
, sum(p82) as "PIALANG"
, sum(p83) as "PARANORMAL"
, sum(p84) as "PEDAGANG"
, sum(p85) as "PERANGKAT DESA"
, sum(p86) as "KEPALA DESA"
, sum(p87) as "BIARAWATI"
, sum(p88) as "WIRASWASTA"
, sum(p89) as "LAINNYA"
, sum(surat_pndh_dalam_desa) as "SURAT PINDAH DALAM DESA"
, sum(surat_pndh_antar_desa) as "SURAT PINDAH ANTAR DESA"
, sum(surat_pndh_antar_kec) as "SURAT PINDAH ANTAR KECAMATAN"
, sum(surat_pndh_antar_kab) as "SURAT PINDAH ANTAR KABUPATEN"
, sum(surat_pndh_antar_prop) as "SURAT PINDAH ANTAR PROPINSI"
, sum(ttl_surat_pndh) as "TOTAL SURAT PINDAH"
, sum(bio_pndh_dalam_desa) as "BIODATA PINDAH DALAM DESA"
, sum(bio_pndh_antar_desa) as "BIODATA PINDAH ANTAR DESA"
, sum(bio_pndh_antar_kec) as "BIODATA PINDAH ANTAR KECAMATAN"
, sum(bio_pndh_antar_kab) as "BIODATA PINDAH ANTAR KABUPATEN"
, sum(bio_pndh_antar_prop) as "BIODATA PINDAH ANTAR PROPINSI"
, sum(total_bio_pndh) as "TOTAL BIODATA PINDAH"
, sum(datang_dalam_desa) as "SURAT DATANG DALAM DESA"
, sum(datang_antar_desa) as "SURAT DATANG ANTAR DESA"
, sum(datang_antar_kec) as "SURAT DATANG ANTAR KECAMATAN"
, sum(datang_antar_kab) as "SURAT DATANG ANTAR KABUPATEN"
, sum(datang_antar_prop) as "SURAT DATANG ANTAR PROPINSI"
, sum(total_datang) as "TOTAL SURAT DATANG"
, sum(bio_datang_dlm_desa) as "BIODATA DATANG DALAM DESA"
, sum(bio_datang_antar_desa) as "BIODATA DATANG ANTAR DESA"
, sum(bio_datang_antar_kec) as "BIODATA DATANG ANTAR KECAMATAN"
, sum(bio_datang_antar_kab) as "BIODATA DATANG ANTAR KABUPATEN"
, sum(bio_datang_antar_prop) as "BIODATA DATANG ANTAR PROP"
, sum(total_bio_datang) as "TOTAL BIODATA DATANG"
 from 
(SELECT NO_PROP np, NO_KAB nk, NO_KEC nc ,NO_KEL nl FROM SETUP_KEL WHERE (NO_PROP, NO_KAB) IN 
(SELECT NO_PROP, NO_KAB FROM SETUP_APLIKASI)) a
left join 
(select NO_PROP, NO_KAB, NO_KEC,NO_KEL
, sum (case when jenis_klmin =1 then 1 end) AS laki
, sum (case when jenis_klmin =2 then 1 end) AS perempuan
, sum (case when jenis_klmin in (1,2) then 1 else 0 end) AS jduk 
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<1) and jenis_klmin=1  then 1 else 0 end) laki_lahir_thn_ini
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<1) and jenis_klmin=2  then 1 else 0 end) perempuan_lahir_thn_ini
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<1) then 1 else 0 end) total_lahir_thn_ini
, sum (case when ((floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=17) or stat_kwn>1) and jenis_klmin=1  then 1 else 0 end) wktp_lk 
, sum (case when ((floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=17) or stat_kwn>1) and jenis_klmin=2  then 1 else 0 end) wktp_pr
, sum (case when ((floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=17) or stat_kwn>1) then 1 else 0 end) wktp
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<5)   then 1 else 0 end) usia05
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=5) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<10)  then 1 else 0 end) usia0510
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=10) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<15)  then 1 else 0 end) usia1015
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=15) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<20)  then 1 else 0 end) usia1520
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=20) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<25)  then 1 else 0 end) usia2025
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=25) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<30)  then 1 else 0 end) usia2530
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=30) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<35)  then 1 else 0 end) usia3035
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=35) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<40)  then 1 else 0 end) usia3540
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=40) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<45)  then 1 else 0 end) usia4045
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=45) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<50)  then 1 else 0 end) usia4550
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=50) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<55)  then 1 else 0 end) usia5055
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=55) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<60)  then 1 else 0 end) usia5560
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=60) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<65)  then 1 else 0 end) usia6065
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=65) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<70)  then 1 else 0 end) usia6570
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=70) and (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<75)  then 1 else 0 end) usia7075
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)>=75) then 1 else 0 end) usia75up
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<17) and stat_kwn<2 then 1 else 0 end) anak_bwh17
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<17) and stat_kwn<2 and jenis_klmin=1 then 1 else 0 end) anak_bwh17_lk
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<17) and stat_kwn<2 and jenis_klmin=2 then 1 else 0 end) anak_bwh17_pr
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) then 1 else 0 end) anak_bwh18
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) and jenis_klmin=1 then 1 else 0 end) anak_bwh18_lk
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) and jenis_klmin=2 then 1 else 0 end) anak_bwh18_pr
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) and (akta_lhr = 2 or length(no_akta_lhr)>4) then 1 else 0 end) anak_bwh18_akta
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) and jenis_klmin=1 and (akta_lhr = 2 or length(no_akta_lhr)>4)  then 1 else 0 end) anak_bwh18_akta_lk
, sum (case when (floor(months_between(to_date('30-06-2020','dd-mm-yyyy'),tgl_lhr)/12)<=18) and jenis_klmin=2 and (akta_lhr = 2 or length(no_akta_lhr)>4)  then 1 else 0 end) anak_bwh18_akta_pr
, sum (case when jenis_klmin=1 and stat_kwn=1 then 1 else 0 end) laki_belum_kawin
, sum (case when jenis_klmin=2 and stat_kwn=1 then 1 else 0 end) perempuan_belum_kawin
, sum (case when stat_kwn=1 then 1 else 0 end) total_belum_kawin
, sum (case when jenis_klmin=1 and stat_kwn=2 then 1 else 0 end) laki_kawin
, sum (case when jenis_klmin=2 and stat_kwn=2 then 1 else 0 end) perempuan_kawin
, sum (case when stat_kwn=2 then 1 else 0 end) total_kawin
, sum (case when jenis_klmin=1 and stat_kwn=3 then 1 else 0 end) laki_cerai_hidup
, sum (case when jenis_klmin=2 and stat_kwn=3 then 1 else 0 end) perempuan_cerai_hidup
, sum (case when stat_kwn=3 then 1 else 0 end) total_cerai_hidup
, sum (case when jenis_klmin=1 and stat_kwn=4 then 1 else 0 end) laki_cerai_mati
, sum (case when jenis_klmin=2 and stat_kwn=4 then 1 else 0 end) perempuan_cerai_mati
, sum (case when stat_kwn=4 then 1 else 0 end) total_cerai_mati
, sum (case when jenis_klmin=1 and stat_kwn=2 and (akta_kwn=2 or length(no_akta_kwn)>4) then 1 else 0 end) laki_kawin_trctt
, sum (case when jenis_klmin=2 and stat_kwn=2  and (akta_kwn=2 or length(no_akta_kwn)>4) then 1 else 0 end) perempuan_kawin_trctt
, sum (case when stat_kwn=2  and (akta_kwn=2 or length(no_akta_kwn)>4) then 1 else 0 end) total_kawin_trctt
, sum (case when jenis_klmin=1 and stat_kwn=3  and (akta_crai=2 or length(no_akta_crai)>4) then 1 else 0 end) laki_cerai_hidup_trctt
, sum (case when jenis_klmin=2 and stat_kwn=3 and (akta_crai=2 or length(no_akta_crai)>4) then 1 else 0 end) perempuan_cerai_hidup_trctt
, sum (case when stat_kwn=3 and (akta_crai=2 or length(no_akta_crai)>4) then 1 else 0 end) total_cerai_hidup_trctt
, sum (case when jenis_klmin=1 and stat_kwn=4 and (akta_crai=2 or length(no_akta_crai)>4)  then 1 else 0 end) laki_cerai_mati_trctt
, sum (case when jenis_klmin=2 and stat_kwn=4 and (akta_crai=2 or length(no_akta_crai)>4) then 1 else 0 end) perempuan_cerai_mati_trctt
, sum (case when stat_kwn=4 and (akta_crai=2 or length(no_akta_crai)>4) then 1 else 0 end) total_cerai_mati_trctt
, sum (case when agama =1 then 1 else 0 end) AS islam
, sum (case when agama =2 then 1 else 0 end) AS kristen
, sum (case when agama =3 then 1 else 0 end) AS khatolik
, sum (case when agama =4 then 1 else 0 end) AS hindu
, sum (case when agama =5 then 1 else 0 end) AS budha
, sum (case when agama =6 then 1 else 0 end) AS KHONGHUCU
, sum (case when agama =7 then 1 else 0 end) AS kepercayaan
, sum (case when pddk_akh =1 then 1 else 0 end) AS tbsklh
, sum (case when pddk_akh =2 then 1 else 0 end) AS btsd
, sum (case when pddk_akh =3 then 1 else 0 end) AS sd
, sum (case when pddk_akh =4 then 1 else 0 end) AS smp
, sum (case when pddk_akh =5 then 1 else 0 end) AS sma
, sum (case when pddk_akh =6 then 1 else 0 end) AS d1d2
, sum (case when pddk_akh =7 then 1 else 0 end) AS d3
, sum (case when pddk_akh =8 then 1 else 0 end) AS d4s1
, sum (case when pddk_akh =9 then 1 else 0 end) AS s2
, sum (case when pddk_akh =10 then 1 else 0 end) AS s3
, sum (case when jenis_pkrjn =1 then 1 else 0 end) AS p1
, sum (case when jenis_pkrjn =2 then 1 else 0 end) AS p2
, sum (case when jenis_pkrjn =3 then 1 else 0 end) AS p3
, sum (case when jenis_pkrjn =4 then 1 else 0 end) AS p4
, sum (case when jenis_pkrjn =5 then 1 else 0 end) AS p5
, sum (case when jenis_pkrjn =6 then 1 else 0 end) AS p6
, sum (case when jenis_pkrjn =7 then 1 else 0 end) AS p7
, sum (case when jenis_pkrjn =8 then 1 else 0 end) AS p8
, sum (case when jenis_pkrjn =9 then 1 else 0 end) AS p9
, sum (case when jenis_pkrjn =10 then 1 else 0 end) AS p10
, sum (case when jenis_pkrjn =11 then 1 else 0 end) AS p11
, sum (case when jenis_pkrjn =12 then 1 else 0 end) AS p12
, sum (case when jenis_pkrjn =13 then 1 else 0 end) AS p13
, sum (case when jenis_pkrjn =14 then 1 else 0 end) AS p14
, sum (case when jenis_pkrjn =15 then 1 else 0 end) AS p15
, sum (case when jenis_pkrjn =16 then 1 else 0 end) AS p16
, sum (case when jenis_pkrjn =17 then 1 else 0 end) AS p17
, sum (case when jenis_pkrjn =18 then 1 else 0 end) AS p18
, sum (case when jenis_pkrjn =19 then 1 else 0 end) AS p19
, sum (case when jenis_pkrjn =20 then 1 else 0 end) AS p20
, sum (case when jenis_pkrjn =21 then 1 else 0 end) AS p21
, sum (case when jenis_pkrjn =22 then 1 else 0 end) AS p22
, sum (case when jenis_pkrjn =23 then 1 else 0 end) AS p23
, sum (case when jenis_pkrjn =24 then 1 else 0 end) AS p24
, sum (case when jenis_pkrjn =25 then 1 else 0 end) AS p25
, sum (case when jenis_pkrjn =26 then 1 else 0 end) AS p26
, sum (case when jenis_pkrjn =27 then 1 else 0 end) AS p27
, sum (case when jenis_pkrjn =28 then 1 else 0 end) AS p28
, sum (case when jenis_pkrjn =29 then 1 else 0 end) AS p29
, sum (case when jenis_pkrjn =30 then 1 else 0 end) AS p30
, sum (case when jenis_pkrjn =31 then 1 else 0 end) AS p31
, sum (case when jenis_pkrjn =32 then 1 else 0 end) AS p32
, sum (case when jenis_pkrjn =33 then 1 else 0 end) AS p33
, sum (case when jenis_pkrjn =34 then 1 else 0 end) AS p34
, sum (case when jenis_pkrjn =35 then 1 else 0 end) AS p35
, sum (case when jenis_pkrjn =36 then 1 else 0 end) AS p36
, sum (case when jenis_pkrjn =37 then 1 else 0 end) AS p37
, sum (case when jenis_pkrjn =38 then 1 else 0 end) AS p38
, sum (case when jenis_pkrjn =39 then 1 else 0 end) AS p39
, sum (case when jenis_pkrjn =40 then 1 else 0 end) AS p40
, sum (case when jenis_pkrjn =41 then 1 else 0 end) AS p41
, sum (case when jenis_pkrjn =42 then 1 else 0 end) AS p42
, sum (case when jenis_pkrjn =43 then 1 else 0 end) AS p43
, sum (case when jenis_pkrjn =44 then 1 else 0 end) AS p44
, sum (case when jenis_pkrjn =45 then 1 else 0 end) AS p45
, sum (case when jenis_pkrjn =46 then 1 else 0 end) AS p46
, sum (case when jenis_pkrjn =47 then 1 else 0 end) AS p47
, sum (case when jenis_pkrjn =48 then 1 else 0 end) AS p48
, sum (case when jenis_pkrjn =49 then 1 else 0 end) AS p49
, sum (case when jenis_pkrjn =50 then 1 else 0 end) AS p50
, sum (case when jenis_pkrjn =51 then 1 else 0 end) AS p51
, sum (case when jenis_pkrjn =52 then 1 else 0 end) AS p52
, sum (case when jenis_pkrjn =53 then 1 else 0 end) AS p53
, sum (case when jenis_pkrjn =54 then 1 else 0 end) AS p54
, sum (case when jenis_pkrjn =55 then 1 else 0 end) AS p55
, sum (case when jenis_pkrjn =56 then 1 else 0 end) AS p56
, sum (case when jenis_pkrjn =57 then 1 else 0 end) AS p57
, sum (case when jenis_pkrjn =58 then 1 else 0 end) AS p58
, sum (case when jenis_pkrjn =59 then 1 else 0 end) AS p59
, sum (case when jenis_pkrjn =60 then 1 else 0 end) AS p60
, sum (case when jenis_pkrjn =61 then 1 else 0 end) AS p61
, sum (case when jenis_pkrjn =62 then 1 else 0 end) AS p62
, sum (case when jenis_pkrjn =63 then 1 else 0 end) AS p63
, sum (case when jenis_pkrjn =64 then 1 else 0 end) AS p64
, sum (case when jenis_pkrjn =65 then 1 else 0 end) AS p65
, sum (case when jenis_pkrjn =66 then 1 else 0 end) AS p66
, sum (case when jenis_pkrjn =67 then 1 else 0 end) AS p67
, sum (case when jenis_pkrjn =68 then 1 else 0 end) AS p68
, sum (case when jenis_pkrjn =69 then 1 else 0 end) AS p69
, sum (case when jenis_pkrjn =70 then 1 else 0 end) AS p70
, sum (case when jenis_pkrjn =71 then 1 else 0 end) AS p71
, sum (case when jenis_pkrjn =72 then 1 else 0 end) AS p72
, sum (case when jenis_pkrjn =73 then 1 else 0 end) AS p73
, sum (case when jenis_pkrjn =74 then 1 else 0 end) AS p74
, sum (case when jenis_pkrjn =75 then 1 else 0 end) AS p75
, sum (case when jenis_pkrjn =76 then 1 else 0 end) AS p76
, sum (case when jenis_pkrjn =77 then 1 else 0 end) AS p77
, sum (case when jenis_pkrjn =78 then 1 else 0 end) AS p78
, sum (case when jenis_pkrjn =79 then 1 else 0 end) AS p79
, sum (case when jenis_pkrjn =80 then 1 else 0 end) AS p80
, sum (case when jenis_pkrjn =81 then 1 else 0 end) AS p81
, sum (case when jenis_pkrjn =82 then 1 else 0 end) AS p82
, sum (case when jenis_pkrjn =83 then 1 else 0 end) AS p83
, sum (case when jenis_pkrjn =84 then 1 else 0 end) AS p84
, sum (case when jenis_pkrjn =85 then 1 else 0 end) AS p85
, sum (case when jenis_pkrjn =86 then 1 else 0 end) AS p86
, sum (case when jenis_pkrjn =87 then 1 else 0 end) AS p87
, sum (case when jenis_pkrjn =88 then 1 else 0 end) AS p88
, sum (case when jenis_pkrjn =89 then 1 else 0 end) AS p89
from biodata_wni_202001
group by NO_PROP, NO_KAB, NO_KEC,NO_KEL) b 
on np=b.no_prop and nk=b.no_kab and nc=b.no_kec and nl=b.no_kel
left join
(select no_prop, no_kab, no_kec, no_kel, count(no_kk) JUM_KK FROM DATA_KELUARGA_202001
group by NO_PROP, NO_KAB, NO_KEC,NO_KEL) c 
on np=c.no_prop and nk=c.no_kab and nc=c.no_kec and nl=c.no_kel
left join
(select 
no_prop, 
no_kab,
no_kec,no_kel
,sum(datang1) datang_dalam_desa
,sum(datang2) datang_antar_desa
,sum(datang3) datang_antar_kec
,sum(datang4) datang_antar_kab
,sum(datang5) datang_antar_prop
,sum(datangAll) total_datang
 from (
select b.no_datang, b.no_prop, b.NO_KAB, b.NO_KEC, b.no_kel, 
b.klasifikasi_pindah, trunc(b.tgl_datang) tgl_datang ,
case when klasifikasi_pindah =1 then 1 ELSE 0 END datang1,
case when klasifikasi_pindah =2 then 1 ELSE 0 END datang2,
case when klasifikasi_pindah =3 then 1 ELSE 0 END datang3,
case when klasifikasi_pindah =4 then 1 ELSE 0 END datang4,
case when klasifikasi_pindah =5 then 1 ELSE 0 END datang5,
case when klasifikasi_pindah in (1,2,3,4,5) then 1 ELSE 0 END datangAll
from datang_header b 
where  
(b.no_prop, b.NO_KAB) in (select no_prop,no_kab from setup_aplikasi))
-- ganti tahun bulan sesuai selera
where to_char(tgl_datang,'yyyymm') >= '202001' 
and to_char(tgl_datang,'yyyymm') <= '202006'
group by no_prop, no_kab, no_kec, no_kel) d
on np=d.no_prop and nk=d.no_kab and nc=d.no_kec and nl=d.no_kel
left join 
(select 
no_prop, 
no_kab,
no_kec,no_kel
,sum(datang1) bio_datang_dlm_desa
,sum(datang2) bio_datang_antar_desa
,sum(datang3) bio_datang_antar_kec
,sum(datang4) bio_datang_antar_kab
,sum(datang5) bio_datang_antar_prop
,sum(datangAll) total_bio_datang
 from (
select a.no_datang, a.nik, b.no_prop, b.NO_KAB, b.NO_KEC, b.no_kel, 
b.klasifikasi_pindah, trunc(b.tgl_datang) tgl_datang ,
case when klasifikasi_pindah =1 then 1 ELSE 0 END datang1,
case when klasifikasi_pindah =2 then 1 ELSE 0 END datang2,
case when klasifikasi_pindah =3 then 1 ELSE 0 END datang3,
case when klasifikasi_pindah =4 then 1 ELSE 0 END datang4,
case when klasifikasi_pindah =5 then 1 ELSE 0 END datang5,
case when klasifikasi_pindah in (1,2,3,4,5) then 1 ELSE 0 END datangAll
from datang_detail a, datang_header b 
where a.no_datang=b.no_datang
and 
(b.no_prop, b.NO_KAB) in (select no_prop,no_kab from setup_aplikasi))
-- ganti tahun bulan sesuai selera
where to_char(tgl_datang,'yyyymm') >= '202001' 
and to_char(tgl_datang,'yyyymm') <= '202006'
group by no_prop, no_kab, no_kec, no_kel) e
on np=e.no_prop and nk=e.no_kab and nc=e.no_kec and nl=e.no_kel
left join 
(select 
from_no_prop, from_no_kab, from_no_kec,from_no_kel
,sum(pindah1) surat_pndh_dalam_desa
,sum(pindah2) surat_pndh_antar_desa
,sum(pindah3) surat_pndh_antar_kec
,sum(pindah4) surat_pndh_antar_kab
,sum(pindah5) surat_pndh_antar_prop
,sum(pindahAll) ttl_surat_pndh
 from (
select b.no_pindah, b.from_no_prop, b.FROM_NO_KAB, b.FROM_NO_KEC, b.from_no_kel, 
b.klasifikasi_pindah, trunc(b.created_date) created_date ,
case when klasifikasi_pindah =1 then 1 ELSE 0 END pindah1,
case when klasifikasi_pindah =2 then 1 ELSE 0 END pindah2,
case when klasifikasi_pindah =3 then 1 ELSE 0 END pindah3,
case when klasifikasi_pindah =4 then 1 ELSE 0 END pindah4,
case when klasifikasi_pindah =5 then 1 ELSE 0 END pindah5,
case when klasifikasi_pindah in (1,2,3,4,5) then 1 ELSE 0 END pindahAll
from pindah_header b 
where 
(b.from_no_prop, b.FROM_NO_KAB) in (select no_prop,no_kab from setup_aplikasi))
-- ganti tahun bulan sesuai selera
where to_char(created_date,'yyyymm') >= '202001' 
and to_char(created_date,'yyyymm') <= '202006' 
group by from_no_kec, from_no_prop, from_no_kab,from_no_kel) f
on np=f.from_no_prop and nk=f.FROM_NO_KAB and nc=f.from_no_kec and nl=f.from_no_kel
left join 
(select 
from_no_prop, from_no_kab, from_no_kec, from_no_kel
,sum(pindah1) bio_pndh_dalam_desa
,sum(pindah2) bio_pndh_antar_desa
,sum(pindah3) bio_pndh_antar_kec
,sum(pindah4) bio_pndh_antar_kab
,sum(pindah5) bio_pndh_antar_prop
,sum(pindahAll) total_bio_pndh
 from (
select a.no_pindah, a.nik, b.from_no_prop, b.FROM_NO_KAB, b.FROM_NO_KEC, b.from_no_kel, 
b.klasifikasi_pindah, trunc(b.created_date) created_date ,
case when klasifikasi_pindah =1 then 1 ELSE 0 END pindah1,
case when klasifikasi_pindah =2 then 1 ELSE 0 END pindah2,
case when klasifikasi_pindah =3 then 1 ELSE 0 END pindah3,
case when klasifikasi_pindah =4 then 1 ELSE 0 END pindah4,
case when klasifikasi_pindah =5 then 1 ELSE 0 END pindah5,
case when klasifikasi_pindah in (1,2,3,4,5) then 1 ELSE 0 END pindahAll
from pindah_detail a, pindah_header b 
where a.no_pindah=b.no_pindah 
and 
(b.from_no_prop, b.FROM_NO_KAB) in (select no_prop,no_kab from setup_aplikasi))
-- ganti tahun bulan sesuai selera
where to_char(created_date,'yyyymm') >= '202001' 
and to_char(created_date,'yyyymm') <= '202006' 
group by from_no_kec, from_no_prop, from_no_kab,from_no_kel) g
on np=g.from_no_prop and nk=g.FROM_NO_KAB and nc=g.from_no_kec and nl=g.from_no_kel
left join 
(SELECT ADM_NO_PROV,ADM_NO_KAB, ADM_NO_KEC, ADM_NO_KEL
, SUM(MATI_LAKI) mati_laki
, SUM(MATI_PEREMPUAN) mati_pr
, SUM(MATI_PEREMPUAN)+ SUM(MATI_LAKI) total_mati
FROM (
SELECT ADM_NO_PROV,ADM_NO_KAB, ADM_NO_KEC, ADM_NO_KEL, MATI_JNS_KELAMIN, MATI_TGL_MATI,
CASE WHEN MATI_JNS_KELAMIN = 1 THEN 1 ELSE 0 END MATI_LAKI,
CASE WHEN MATI_JNS_KELAMIN = 2 THEN 1 ELSE 0 END MATI_PEREMPUAN
FROM CAPIL_MATI 
where to_char(MATI_TGL_MATI,'yyyymm') >= '202001' and to_char(MATI_TGL_MATI,'yyyymm') <= '202006')
GROUP BY ADM_NO_PROV,ADM_NO_KAB, ADM_NO_KEC, ADM_NO_KEL) h
on np=h.ADM_NO_PROV and nk=h.ADM_NO_KAB and nc=h.ADM_NO_KEC and nl=h.ADM_NO_KEL
group by ROLLUP (nc, nl), np, nk
order by np,nk,nc,nl;

Subscribe to receive free email updates:

0 Response to "Script Mengolah DKB tahun 2020 Semester 1 "

Posting Komentar