SELECT 
	NIP,
	NM_PEG,
	NM_JABATAN,
	NM_UNIT_ES1,
	NM_UNIT_ES2,
	NM_UNIT_ES3,
	NM_UNIT_ES4,
	NM_UNIT_ES5,
	JNS_JABATAN_CUR,
	KD_ESELON
FROM 
	SPG_DATA_CURRENT
WHERE
	STS_PENSIUN = '0'
	AND NM_JABATAN IN ('Kepala Departemen', 'Kepala Grup', 'Kepala Grup (Direktur)', 'Kepala OJK Regional', 'Kepala OJK')
	AND KD_ESELON >= 11
ORDER BY
	NM_UNIT_ES1,
	NM_UNIT_ES2,
	NM_UNIT_ES3,
	NM_UNIT_ES4,
	NM_UNIT_ES5,
	NIP

--SELECT MIN(KD_ESELON), kode_satker_organisasi FROM SPG_08_UNIT_ORGANISASI WHERE nama_satker_organisasi LIKE '%aplikasi%' GROUP BY kode_satker_organisasi

SELECT
	sdc.NIP,
	sdc.NM_PEG,
	sdc.NM_JABATAN,
	sdc.NM_UNIT_ES1,
	sdc.NM_UNIT_ES2,
	sdc.NM_UNIT_ES3,
	sdc.NM_UNIT_ES4,
	sdc.NM_UNIT_ES5,
	satker.kode_satker_organisasi,
	satker.nama_satker_organisasi,
	satker.KD_ESELON,
	sdc.JNS_JABATAN_CUR
FROM
	(
		SELECT --DISTINCT
			MIN(KD_ESELON) AS KD_ESELON,
			kode_satker_organisasi,
			nama_satker_organisasi
		FROM
			dbo.MARP_UNIT_ORGANISASI_ACTIVE_ONLY_v2
		GROUP BY
			kode_satker_organisasi,
			nama_satker_organisasi
	) AS satker -- list satker aktif
	INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo 
		ON satker.kode_satker_organisasi = suo.kode_satker_organisasi
	INNER JOIN
	SPG_DATA_CURRENT AS sdc 
		ON suo.KD_UNIT_ORG = sdc.KD_UNIT_ORG 
		AND satker.KD_ESELON = sdc.KD_ESELON 
		AND sdc.STS_PENSIUN = '0'
WHERE
	sdc.KD_ESELON >= 11
	AND JNS_JABATAN_CUR = 'struktural'
ORDER BY
	sdc.NM_UNIT_ES1,
	sdc.NM_UNIT_ES2,
	sdc.NM_UNIT_ES3,
	sdc.NM_UNIT_ES4,
	sdc.NM_UNIT_ES5,
	sdc.NIP


--SELECT KD_UNIT_ORG FROM SPG_DATA_CURRENT where nip in ('02460', '00113')
SELECT * FROM SPG_08_UNIT_ORGANISASI WHERE KD_UNIT_ORG IN (SELECT KD_UNIT_ORG FROM SPG_DATA_CURRENT where nip in ('02460', '00113'))