Script Mengeluarkan DKB Versi 2018

Langsung aja, masuk ke db SIAK ya
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 "PDDK LAKI2"
, sum(perempuan)  as "PDDK PEREMPUAN"
, sum(jduk)  as "JUMLAH PENDUDUK"
, sum(wktp_lk) as "WKTP LAKI2"
, sum(wktp_pr) as "WKTP PEREMPUAN"
, sum(wktp) as "WAJIB KTP"
, sum(usia05) as "USIA 0-5"
, sum(usia0510) as "USIA 5-10"
, sum(usia1015) as "USIA 10-15"
, sum(usia1520) as "USIA 15-20"
, sum(usia2025) as "USIA 20-25"
, sum(usia2530) as "USIA 25-30"
, sum(usia3035) as "USIA 30-35"
, sum(usia3540) as "USIA 35-40"
, sum(usia4045) as "USIA 40-45"
, sum(usia4550) as "USIA 45-50"
, sum(usia5055) as "USIA 50-55"
, sum(usia5560) as "USIA 55-60"
, sum(usia6065) as "USIA 60-65"
, sum(usia6570) as "USIA 65-70"
, sum(usia7075) as "USIA 70-75"
, sum(usia75up) as "USIA >75"
, sum(JUM_KK) as "JUMLAH KK"
, 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"
 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-2018','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-2018','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-2018','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-2018','dd-mm-yyyy'),tgl_lhr)/12)<5)   then 1 else 0 end) usia05
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=5) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<10)  then 1 else 0 end) usia0510
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=10) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<15)  then 1 else 0 end) usia1015
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=15) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<20)  then 1 else 0 end) usia1520
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=20) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<25)  then 1 else 0 end) usia2025
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=25) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<30)  then 1 else 0 end) usia2530
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=30) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<35)  then 1 else 0 end) usia3035
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=35) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<40)  then 1 else 0 end) usia3540
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=40) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<45)  then 1 else 0 end) usia4045
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=45) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<50)  then 1 else 0 end) usia4550
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=50) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<55)  then 1 else 0 end) usia5055
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=55) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<60)  then 1 else 0 end) usia5560
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=60) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<65)  then 1 else 0 end) usia6065
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=65) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<70)  then 1 else 0 end) usia6570
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=70) and (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)<75)  then 1 else 0 end) usia7075
, sum (case when (floor(months_between(to_date('30-06-2018','dd-mm-yyyy'),tgl_lhr)/12)>=75)   then 1 else 0 end) usia75up
, 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_201801
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_201801
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
group by ROLLUP (np,nk,nc,nl)
order by np,nk,nc,nl;

Subscribe to receive free email updates:

0 Response to "Script Mengeluarkan DKB Versi 2018"

Posting Komentar