--SELECT
--	COUNT(a.ID)
--FROM
--	REC_PELAMAR_INTERNAL AS a INNER JOIN
--	REC_RENCANA_PEMENUHAN_PEG AS b ON a.ID_FORMASI = b.ID_FORMASI INNER JOIN
--	dbo.REC_PELAMAR_RANKING AS pr ON a.ID = pr.ID_PELAMAR AND pr.MPP_ID = b.MPP_ID
--WHERE
--	b.MPP_ID = 'MPP-000002'
--	AND COALESCE(a.REKOMENDASI_PIMPINAN,'') = 'Y'
--	AND pr.SHORT_LIST = 1

SELECT
	*
FROM
	(
		SELECT DISTINCT
			b.BIDANG,
			b.SATKER,
			b.DIREKTORAT,
			b.FORMASI,
			LEFT(b.KD_UNIT_ORG,3) AS bid,
			b.KD_SATKER,
			b.KD_GOL,
			b.JNS_JABATAN
		FROM
			REC_PELAMAR_INTERNAL AS a INNER JOIN
			REC_RENCANA_PEMENUHAN_PEG AS b ON a.ID_FORMASI = b.ID_FORMASI 
		WHERE
			b.MPP_ID = 'MPP-000002'
			AND COALESCE(a.REKOMENDASI_PIMPINAN,'') = 'Y'
			AND a.ID_FORMASI NOT IN (
								SELECT
									a.ID_FORMASI
								FROM
									REC_PELAMAR_INTERNAL AS a INNER JOIN
									REC_RENCANA_PEMENUHAN_PEG AS b ON a.ID_FORMASI = b.ID_FORMASI INNER JOIN
									dbo.REC_PELAMAR_RANKING AS pr ON a.ID = pr.ID_PELAMAR AND pr.MPP_ID = b.MPP_ID
								WHERE
									b.MPP_ID = 'MPP-000002'
									AND COALESCE(a.REKOMENDASI_PIMPINAN,'') = 'Y'
									AND pr.SHORT_LIST = 1
								)
	) AS x
ORDER BY
	bid,
	x.KD_SATKER,
	x.KD_GOL DESC,
	x.JNS_JABATAN DESC

