Script Mencari Data Cetak KK, KIA, Kelahiran, Perkawinan, Perceraian, Rekam KTP Perbulan



------ KK
SELECT NO_PROP, NO_KAB, getnamakec(NO_KEC,no_kab, no_prop) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_PRINT_KK
WHERE to_CHAR(printed_date,'YYYY') = '2019'
group by
grouping sets((no_prop, no_kab,no_kec)
,()
);

------ KIA
SELECT NO_PROP, NO_KAB, getnamakec(NO_KEC,no_kab, no_prop) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_KIA_PRINT
WHERE to_CHAR(printed_date,'YYYY') = '2019'
---and NIK IN (SELECT NIK FROM BIODATA_WNI WHERE floor(months_between('31-JAN-2019',tgl_lhr)/12) <=5)
group by
grouping sets((no_prop, no_kab,no_kec)
,()
);

------ LAHIR
SELECT b.adm_NO_PROv, b.adm_NO_KAB, getnamakec(b.adm_NO_KEC,b.adm_no_kab, b.adm_no_prov) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_PRINT_KELAHIRAN a, capil_lahir b
WHERE to_CHAR(printed_date,'YYYY') = '2019' and
a.no_dokumen = b.adm_akta_no
---and NIK IN (SELECT NIK FROM BIODATA_WNI WHERE floor(months_between('31-JAN-2019',tgl_lhr)/12) <=5)
group by
grouping sets((b.adm_no_prov, b.adm_no_kab,b.adm_no_kec)
,()
);


------ KAWIN
SELECT b.adm_NO_PROv, b.adm_NO_KAB, getnamakec(b.adm_NO_KEC,b.adm_no_kab, b.adm_no_prov) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_PRINT_PERKAWINAN a, capil_KAWIN b
WHERE to_CHAR(printed_date,'YYYY') = '2019' and
a.no_dokumen = b.adm_akta_no
---and NIK IN (SELECT NIK FROM BIODATA_WNI WHERE floor(months_between('31-JAN-2019',tgl_lhr)/12) <=5)
group by
grouping sets((b.adm_no_prov, b.adm_no_kab,b.adm_no_kec)
,()
);


------ CERAI
SELECT b.adm_NO_PROv, b.adm_NO_KAB, getnamakec(b.adm_NO_KEC,b.adm_no_kab, b.adm_no_prov) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_PRINT_PERCERAIAN a, capil_CERAI b
WHERE to_CHAR(printed_date,'YYYY') = '2019' and
a.no_dokumen = b.adm_akta_no
---and NIK IN (SELECT NIK FROM BIODATA_WNI WHERE floor(months_between('31-JAN-2019',tgl_lhr)/12) <=5)
group by
grouping sets((b.adm_no_prov, b.adm_no_kab,b.adm_no_kec)
,()
);


------ KTP
SELECT b.NO_PROP, b.NO_KAB, getnamakec(b.NO_KEC,b.no_kab, b.no_proP) nama_kec
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(PERSONALIZED_DATE,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM CARD_MANAGEMENT@ORCL a, DEMOGRAPHICS@ORCL b
WHERE to_CHAR(PERSONALIZED_DATE,'YYYY') = '2019' and
a.NIK = b.NIK AND
--- ganti Kode Prop Kab
b.No_prop = xx and b.no_kab = xx
group by
grouping sets((b.No_proP, b.no_kab,b.no_kec)
,()
);


------ KTP REKAM
SELECT NO_PROP, NO_KAB, getnamakec(NO_KEC,no_kab, no_proP) nama_kec
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(CREATED,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(CREATED,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM DEMOGRAPHICS@LOCAL
WHERE to_CHAR(CREATED,'YYYY') = '2019' AND
--- ganti Kode Prop Kab
No_prop = xx and no_kab = xx
group by
grouping sets((No_proP, no_kab,no_kec)
,()
);

------ mati
SELECT b.adm_NO_PROv, b.adm_NO_KAB, getnamakec(b.adm_NO_KEC,b.adm_no_kab, b.adm_no_prov) nama_kec
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '01' THEN 1 ELSE 0 END) BLN_01
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '02' THEN 1 ELSE 0 END) BLN_02
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '03' THEN 1 ELSE 0 END) BLN_03
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '04' THEN 1 ELSE 0 END) BLN_04
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '05' THEN 1 ELSE 0 END) BLN_05
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '06' THEN 1 ELSE 0 END) BLN_06
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '07' THEN 1 ELSE 0 END) BLN_07
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '08' THEN 1 ELSE 0 END) BLN_08
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '09' THEN 1 ELSE 0 END) BLN_09
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '10' THEN 1 ELSE 0 END) BLN_10
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '11' THEN 1 ELSE 0 END) BLN_11
, SUM(CASE WHEN to_CHAR(printed_date,'MM') = '12' THEN 1 ELSE 0 END) BLN_12
, SUM(CASE WHEN to_CHAR(printed_date,'YYYY') = '2019' THEN 1 ELSE 0 END) TOTAL
FROM T5_SEQN_PRINT_kematian a, capil_mati b
WHERE to_CHAR(printed_date,'YYYY') = '2019' and
a.no_dokumen = b.adm_akta_no
group by
grouping sets((b.adm_no_prov, b.adm_no_kab,b.adm_no_kec)
,()
);

Subscribe to receive free email updates:

0 Response to "Script Mencari Data Cetak KK, KIA, Kelahiran, Perkawinan, Perceraian, Rekam KTP Perbulan"

Posting Komentar