lhfazry

Untitled

Nov 8th, 2020
981
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.        SUM(CASE WHEN pendidikan_terakhir != 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) <= 750000 THEN 1 ELSE 0 END) AS '<750K-1',
  3.        SUM(CASE WHEN pendidikan_terakhir != 1 AND (
  4.               bekerja = 1
  5.               OR penyandang_cacat != 5
  6.               OR status_pernikahan = 2
  7.               OR bekerja_upah = 1
  8.               OR bekerja_tanpa_upah = 1
  9.               OR mengasuh_adik = 1
  10.               OR bermain_sepanjang_hari = 1
  11.               OR rencana_menghentikan_pendidikan_anak = 1
  12.               OR (
  13.                      sekolah_monitor_proses_bdr = 0
  14.                      AND kunjungan_guru_kerumah = 0
  15.                      AND sekolah_memonitor_kelanjutan_pendidikan = 0
  16.                      AND kepala_desa_memonitor_kelanjutan_pendidikan = 0
  17.               )
  18.               OR fas_hp < (SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.hubungan_dgn_kk != 1 AND tdk2.sipbm_id=tp.sipbm_id))
  19.               AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) <= 750000  THEN 1 ELSE 0 END) AS '<750K-2',
  20.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) <= 750000  THEN 1 ELSE 0 END) AS '<750K-3',
  21.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 0 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) <= 750000  THEN 1 ELSE 0 END) AS '<750K-4',
  22.  
  23.        SUM(CASE WHEN pendidikan_terakhir != 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) BETWEEN 750001  AND 1500000  THEN 1 ELSE 0 END) AS '750K-1500K-1',
  24.        SUM(CASE WHEN pendidikan_terakhir != 1 AND (
  25.               bekerja = 1
  26.               OR penyandang_cacat != 5
  27.               OR status_pernikahan = 2
  28.               OR bekerja_upah = 1
  29.               OR bekerja_tanpa_upah = 1
  30.               OR mengasuh_adik = 1
  31.               OR bermain_sepanjang_hari = 1
  32.               OR rencana_menghentikan_pendidikan_anak = 1
  33.               OR (
  34.                      sekolah_monitor_proses_bdr = 0
  35.                      AND kunjungan_guru_kerumah = 0
  36.                      AND sekolah_memonitor_kelanjutan_pendidikan = 0
  37.                      AND kepala_desa_memonitor_kelanjutan_pendidikan = 0
  38.               )
  39.               OR fas_hp < (SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.hubungan_dgn_kk != 1 AND tdk2.sipbm_id=tp.sipbm_id))
  40.               AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) BETWEEN 750001  AND 1500000  THEN 1 ELSE 0 END) AS '750K-1500K-2',
  41.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) BETWEEN 750001  AND 1500000  THEN 1 ELSE 0 END) AS '750K-1500K-3',
  42.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 0 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) BETWEEN 750001  AND 1500000  THEN 1 ELSE 0 END) AS '750K-1500K-4',
  43.  
  44.        SUM(CASE WHEN pendidikan_terakhir != 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) >= 1500001  THEN 1 ELSE 0 END) AS '>1500K',
  45.        SUM(CASE WHEN pendidikan_terakhir != 1 AND (
  46.               bekerja = 1
  47.               OR penyandang_cacat != 5
  48.               OR status_pernikahan = 2
  49.               OR bekerja_upah = 1
  50.               OR bekerja_tanpa_upah = 1
  51.               OR mengasuh_adik = 1
  52.               OR bermain_sepanjang_hari = 1
  53.               OR rencana_menghentikan_pendidikan_anak = 1
  54.               OR (
  55.                      sekolah_monitor_proses_bdr = 0
  56.                      AND kunjungan_guru_kerumah = 0
  57.                      AND sekolah_memonitor_kelanjutan_pendidikan = 0
  58.                      AND kepala_desa_memonitor_kelanjutan_pendidikan = 0
  59.               )
  60.               OR fas_hp < (SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.hubungan_dgn_kk != 1 AND tdk2.sipbm_id=tp.sipbm_id))
  61.               AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) >= 1500001  THEN 1 ELSE 0 END) AS '>1500K-2',
  62.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 1 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) >= 1500001  THEN 1 ELSE 0 END) AS '>1500K-3',
  63.        SUM(CASE WHEN pendidikan_terakhir = 1 AND alasan_tidak_sekolah_covid19 = 0 AND pengeluaran_rata_rata/(SELECT COUNT(*) FROM tb_data_keluarga tdk2 WHERE tdk2.sipbm_id=tp.sipbm_id) >= 1500001  THEN 1 ELSE 0 END) AS '>1500K-4'
  64. FROM tb_pengeluaran tp
  65. INNER JOIN tb_data_keluarga tdk USING(sipbm_id)
  66. INNER JOIN tb_penerangan tpr USING(sipbm_id)
  67. WHERE tdk.hubungan_dgn_kk != '1' AND TIMESTAMPDIFF(YEAR, lahir_tgl, CURDATE()) BETWEEN 4  AND 18;
RAW Paste Data