SELECT
	b.req_id,
	b.ptk_id,
	b.satker,
	b.depkom,
	b.kantor,
	b.masa_dinas_minimal,
	b.sisa_dinas,
	b.tanggal_dinas,
	b.bidang,
	b.NM_PEND_UMUM,
	b.kd_pend_umum,
	b.posisi_jabatan,
	b.kd_jabatan,
	b.tanggal_publish,
	b.tanggal_selesai,
	b.jam_selesai,
	b.mpp_id,
	b.tanggal_buat,
	b.nilai1,
	b.nilai2,
	b.nilai3,
	b.closing_date,
	COALESCE(jml.jml_pelamar, 0) AS jml_pelamar
FROM
	rec_pelamar_internal a
	INNER JOIN
		(
			SELECT
				b.req_id,
				a.ptk_id,
				e.nm_unit_org AS satker,
				f.nm_unit_org AS depkom,
				c.nm_kantor AS kantor,
				m.masa_dinas_minimal,
				m.sisa_dinas,
				m.tanggal_dinas,
				g.nm_unit_org AS bidang,
				n.NM_PEND_UMUM,
				ISNULL(n.KD_PEND_UMUM, 0) AS kd_pend_umum,
				j.posisi AS posisi_jabatan,
				a.kd_jabatan,
				b.tanggal_publish,
				b.tanggal_selesai,
				b.jam_selesai,
				m.kd_gol,
				a.mpp_id,
				m.tanggal_buat,
				h.NM_GOLONGAN,
				m.nilai_kinerja AS nilai1,
				m.nilai_kinerja_1 AS nilai2,
				m.nilai_kinerja_2 AS nilai3,
				DATEADD(DAY, 0, DATEDIFF(DAY, 0, b.tanggal_selesai))
				+ DATEADD(DAY, 0 - DATEDIFF(DAY, 0, b.jam_selesai), b.jam_selesai) AS closing_date,
				d.KD_UNIT_ES1
			FROM
				rec_ptk a
				LEFT JOIN
				rec_permintaan_advertising b ON
					a.ptk_id = b.req_id
				LEFT JOIN
				SPG_08_KANTOR c ON
					a.kd_kantor = c.kd_kantor
				LEFT JOIN
				spg_08_unit_organisasi d ON
					a.kd_unit_org = d.kd_unit_org
				LEFT JOIN
				SPG_08_UNIT_ORGANISASI e ON
					d.KD_UNIT_ES3 = e.KD_UNIT_ORG
				LEFT JOIN
				SPG_08_UNIT_ORGANISASI f ON
					d.KD_UNIT_ES2 = f.KD_UNIT_ORG
				LEFT JOIN
				SPG_08_UNIT_ORGANISASI g ON
					d.KD_UNIT_ES1 = g.KD_UNIT_ORG
				LEFT JOIN
				SPG_08_JABATAN_UNIT j ON
					(
						a.KD_JABATAN = j.KD_JABATAN
						AND a.KD_UNIT_ORG = j.KD_UNIT_ORG
					)
				LEFT JOIN
				SPG_08_JABATAN_STR k ON
					a.kd_jabatan = k.KD_JABATAN_STR
				LEFT JOIN
				SPG_JABATAN_PANGKAT l ON
					a.kd_jabatan = l.KD_JABATAN_STR
				LEFT JOIN
				REC_PERSYARATAN m ON
					(
						m.KD_GOL = l.KD_GOL
						AND m.periode = a.mpp_id
					)
				LEFT JOIN
				spg_golongan h ON
					m.kd_gol = h.kd_gol
				LEFT JOIN
				spg_eselon i ON
					h.KD_ESELON = i.KD_ESELON
				LEFT JOIN
				SPG_JNS_PEND_UMUM n ON
					m.PENDIDIKAN = n.KD_PEND_UMUM
			WHERE
				b.ram_id = 'RAM-000020'
				AND b.rac_id = 'RAC-000001'
		) b ON
		a.req_id = b.req_id
	OUTER APPLY
	(
		SELECT
			REQ_ID	,
			COUNT(*) AS jml_pelamar
		FROM
			rec_pelamar_internal
		WHERE
			REQ_ID	= b.req_id
		GROUP BY
			REQ_ID
	) AS jml
WHERE
	a.nip = '02921'
	--AND b.closing_date >= GETDATE();
--order by b.closing_date asc

--SELECT * FROM spg_pegawai_eligible