SELECT
	sdc.NIP,
	sdc.NM_PEG,
	CASE sdc.STS_PENSIUN
		WHEN '0' THEN 'Aktif'
		WHEN '1' THEN 'Non Aktif'
		ELSE ''
	END AS [STATUS AKTIF],
	--sdc.KD_JABATAN_STR,
	sdc.NM_JABATAN AS JABATAN,
	sdc.NM_ESELON AS [LEVEL JABATAN],
	satu.KET_JENIS_KELOMPOK AS [2022 KET_JENIS_KELOMPOK],
	satu.NK_KALIBRASI AS [2022 NK_KALIBRASI],
	satu.LK AS [2022 LK],
	satu.kd_konversi_pegawai_tertentu AS [2022 konversi_pegawai_tertentu],
	dua.KET_JENIS_KELOMPOK AS [2021 KET_JENIS_KELOMPOK],
	dua.NK_KALIBRASI AS [2021 NK_KALIBRASI],
	dua.LK AS [2021 LK],
	dua.kd_konversi_pegawai_tertentu AS [2021 konversi_pegawai_tertentu],
	tiga.KET_JENIS_KELOMPOK AS [2020 KET_JENIS_KELOMPOK],
	tiga.NK_KALIBRASI AS [2020 NK_KALIBRASI],
	tiga.LK AS [2020 LK],
	tiga.kd_konversi_pegawai_tertentu AS [2020 konversi_pegawai_tertentu],
	empat.KET_JENIS_KELOMPOK AS [2019 KET_JENIS_KELOMPOK],
	empat.NK_KALIBRASI AS [2019 NK_KALIBRASI],
	empat.LK AS [2019 LK],
	empat.kd_konversi_pegawai_tertentu AS [2019 konversi_pegawai_tertentu],
	lima.KET_JENIS_KELOMPOK AS [2018 KET_JENIS_KELOMPOK],
	lima.NK_KALIBRASI AS [2018 NK_KALIBRASI],
	lima.LK AS [2018 LK],
	lima.kd_konversi_pegawai_tertentu AS [2018 konversi_pegawai_tertentu]
FROM
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG LEFT JOIN
	(
		SELECT
			a.NIP,
			--a.JENIS_KELOMPOK,
			CASE a.JENIS_KELOMPOK
				WHEN '1' THEN 'Kepala Subbagian s.d Kepala Departemen'
				WHEN '2' THEN 'Pegawai Dasar s.d Staf'
				ELSE ''
			END AS KET_JENIS_KELOMPOK,
			ISNULL(a.NILAI_KALIBRASI, a.NILAI_KINERJA) AS NK_KALIBRASI, 
			CASE a.BOBOT_MANUAL
				WHEN 1 THEN 'LK5'
				WHEN 2 THEN 'LK4'
				WHEN 3 THEN 'LK3'
				WHEN 4 THEN 'LK2'
				WHEN 5 THEN 'LK1'
				ELSE ''
			END AS LK,
			COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
		FROM
			pfm_rangking_jabatan AS a
			INNER JOIN
			(
				SELECT DISTINCT 
					b.kd_unit_org, 
					b.nm_unit_org,
					COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
				FROM 
					pfm_rangking_status AS a
					INNER JOIN
					spg_08_unit_organisasi AS b ON a.kd_unit_org_admin_satker = b.kd_unit_org
				WHERE
					COALESCE(b.tgl_end, '1900-01-01') = '1900-01-01'
					AND a.pfm_id = 'PFM-000021'
					AND COALESCE(a.STATUS, 0) = '1'
			) AS c ON a.KD_UNIT_ORG_RANGKING = c.KD_UNIT_ORG AND COALESCE(a.kd_konversi_pegawai_tertentu, '0') = c.kd_konversi_pegawai_tertentu
			INNER JOIN
			(
				SELECT  
					MAX(JENIS_KELOMPOK) AS JENIS_KELOMPOK, 
					NIP, 
					PFM_ID
				FROM  
					PFM_RANGKING_JABATAN
				GROUP BY 
					NIP, 
					PFM_ID
			) AS aktif ON a.PFM_ID = aktif.PFM_ID AND a.NIP = aktif.NIP AND a.JENIS_KELOMPOK = aktif.JENIS_KELOMPOK
		WHERE
			a.PFM_ID = 'PFM-000021'
			--AND a.kd_konversi_pegawai_tertentu = '1'
			AND COALESCE(a.PFN_ID, '') <> ''
	) AS satu ON sdc.NIP = satu.NIP LEFT JOIN
	(
		SELECT
			a.NIP,
			--a.JENIS_KELOMPOK,
			CASE a.JENIS_KELOMPOK
				WHEN '1' THEN 'Kepala Subbagian s.d Kepala Departemen'
				WHEN '2' THEN 'Pegawai Dasar s.d Staf'
				ELSE ''
			END AS KET_JENIS_KELOMPOK,
			ISNULL(a.NILAI_KALIBRASI, a.NILAI_KINERJA) AS NK_KALIBRASI, 
			CASE a.BOBOT_MANUAL
				WHEN 1 THEN 'LK5'
				WHEN 2 THEN 'LK4'
				WHEN 3 THEN 'LK3'
				WHEN 4 THEN 'LK2'
				WHEN 5 THEN 'LK1'
				ELSE ''
			END AS LK,
			COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
		FROM
			pfm_rangking_jabatan AS a
			INNER JOIN
			(
				SELECT DISTINCT 
					b.kd_unit_org, 
					b.nm_unit_org,
					COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
				FROM 
					pfm_rangking_status AS a
					INNER JOIN
					spg_08_unit_organisasi AS b ON a.kd_unit_org_admin_satker = b.kd_unit_org
				WHERE
					COALESCE(b.tgl_end, '1900-01-01') = '1900-01-01'
					AND a.pfm_id = 'PFM-000020'
					AND COALESCE(a.STATUS, 0) = '1'
			) AS c ON a.KD_UNIT_ORG_RANGKING = c.KD_UNIT_ORG AND COALESCE(a.kd_konversi_pegawai_tertentu, '0') = c.kd_konversi_pegawai_tertentu
			INNER JOIN
			(
				SELECT  
					MAX(JENIS_KELOMPOK) AS JENIS_KELOMPOK, 
					NIP, 
					PFM_ID
				FROM  
					PFM_RANGKING_JABATAN
				GROUP BY 
					NIP, 
					PFM_ID
			) AS aktif ON a.PFM_ID = aktif.PFM_ID AND a.NIP = aktif.NIP AND a.JENIS_KELOMPOK = aktif.JENIS_KELOMPOK
		WHERE
			a.PFM_ID = 'PFM-000020'
			--AND a.kd_konversi_pegawai_tertentu = '1'
			AND COALESCE(a.PFN_ID, '') <> ''
	) AS dua ON sdc.NIP = dua.NIP LEFT JOIN
	(
		SELECT
			a.NIP,
			--a.JENIS_KELOMPOK,
			CASE a.JENIS_KELOMPOK
				WHEN '1' THEN 'Kepala Subbagian s.d Kepala Departemen'
				WHEN '2' THEN 'Pegawai Dasar s.d Staf'
				ELSE ''
			END AS KET_JENIS_KELOMPOK,
			ISNULL(a.NILAI_KALIBRASI, a.NILAI_KINERJA) AS NK_KALIBRASI, 
			CASE a.BOBOT_MANUAL
				WHEN 1 THEN 'LK5'
				WHEN 2 THEN 'LK4'
				WHEN 3 THEN 'LK3'
				WHEN 4 THEN 'LK2'
				WHEN 5 THEN 'LK1'
				ELSE ''
			END AS LK,
			COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
		FROM
			pfm_rangking_jabatan AS a
			INNER JOIN
			(
				SELECT DISTINCT 
					b.kd_unit_org, 
					b.nm_unit_org,
					COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
				FROM 
					pfm_rangking_status AS a
					INNER JOIN
					spg_08_unit_organisasi AS b ON a.kd_unit_org_admin_satker = b.kd_unit_org
				WHERE
					COALESCE(b.tgl_end, '1900-01-01') = '1900-01-01'
					AND a.pfm_id = 'PFM-000019'
					AND COALESCE(a.STATUS, 0) = '1'
			) AS c ON a.KD_UNIT_ORG_RANGKING = c.KD_UNIT_ORG AND COALESCE(a.kd_konversi_pegawai_tertentu, '0') = c.kd_konversi_pegawai_tertentu
			INNER JOIN
			(
				SELECT  
					MAX(JENIS_KELOMPOK) AS JENIS_KELOMPOK, 
					NIP, 
					PFM_ID
				FROM  
					PFM_RANGKING_JABATAN
				GROUP BY 
					NIP, 
					PFM_ID
			) AS aktif ON a.PFM_ID = aktif.PFM_ID AND a.NIP = aktif.NIP AND a.JENIS_KELOMPOK = aktif.JENIS_KELOMPOK
		WHERE
			a.PFM_ID = 'PFM-000019'
			--AND a.kd_konversi_pegawai_tertentu = '1'
			AND COALESCE(a.PFN_ID, '') <> ''
	) AS tiga ON sdc.NIP = tiga.NIP LEFT JOIN
	(
		SELECT
			a.NIP,
			--a.JENIS_KELOMPOK,
			CASE a.JENIS_KELOMPOK
				WHEN '1' THEN 'Kepala Subbagian s.d Kepala Departemen'
				WHEN '2' THEN 'Pegawai Dasar s.d Staf'
				ELSE ''
			END AS KET_JENIS_KELOMPOK,
			ISNULL(a.NILAI_KALIBRASI, a.NILAI_KINERJA) AS NK_KALIBRASI, 
			CASE a.BOBOT_MANUAL
				WHEN 1 THEN 'LK5'
				WHEN 2 THEN 'LK4'
				WHEN 3 THEN 'LK3'
				WHEN 4 THEN 'LK2'
				WHEN 5 THEN 'LK1'
				ELSE ''
			END AS LK,
			COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
		FROM
			pfm_rangking_jabatan AS a
			INNER JOIN
			(
				SELECT DISTINCT 
					b.kd_unit_org, 
					b.nm_unit_org,
					COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
				FROM 
					pfm_rangking_status AS a
					INNER JOIN
					spg_08_unit_organisasi AS b ON a.kd_unit_org_admin_satker = b.kd_unit_org
				WHERE
					COALESCE(b.tgl_end, '1900-01-01') = '1900-01-01'
					AND a.pfm_id = 'PFM-000018'
					AND COALESCE(a.STATUS, 0) = '1'
			) AS c ON a.KD_UNIT_ORG_RANGKING = c.KD_UNIT_ORG AND COALESCE(a.kd_konversi_pegawai_tertentu, '0') = c.kd_konversi_pegawai_tertentu
			INNER JOIN
			(
				SELECT  
					MAX(JENIS_KELOMPOK) AS JENIS_KELOMPOK, 
					NIP, 
					PFM_ID
				FROM  
					PFM_RANGKING_JABATAN
				GROUP BY 
					NIP, 
					PFM_ID
			) AS aktif ON a.PFM_ID = aktif.PFM_ID AND a.NIP = aktif.NIP AND a.JENIS_KELOMPOK = aktif.JENIS_KELOMPOK
		WHERE
			a.PFM_ID = 'PFM-000018'
			--AND a.kd_konversi_pegawai_tertentu = '1'
			AND COALESCE(a.PFN_ID, '') <> ''
	) AS empat ON sdc.NIP = empat.NIP LEFT JOIN
	(
		SELECT
			a.NIP,
			--a.JENIS_KELOMPOK,
			CASE a.JENIS_KELOMPOK
				WHEN '1' THEN 'Kepala Subbagian s.d Kepala Departemen'
				WHEN '2' THEN 'Pegawai Dasar s.d Staf'
				ELSE ''
			END AS KET_JENIS_KELOMPOK,
			ISNULL(a.NILAI_KALIBRASI, a.NILAI_KINERJA) AS NK_KALIBRASI, 
			CASE a.BOBOT_MANUAL
				WHEN 1 THEN 'LK5'
				WHEN 2 THEN 'LK4'
				WHEN 3 THEN 'LK3'
				WHEN 4 THEN 'LK2'
				WHEN 5 THEN 'LK1'
				ELSE ''
			END AS LK,
			COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
		FROM
			pfm_rangking_jabatan AS a
			INNER JOIN
			(
				SELECT DISTINCT 
					b.kd_unit_org, 
					b.nm_unit_org,
					COALESCE(a.kd_konversi_pegawai_tertentu, '0') AS kd_konversi_pegawai_tertentu
				FROM 
					pfm_rangking_status AS a
					INNER JOIN
					spg_08_unit_organisasi AS b ON a.kd_unit_org_admin_satker = b.kd_unit_org
				WHERE
					COALESCE(b.tgl_end, '1900-01-01') = '1900-01-01'
					AND a.pfm_id = 'PFM-000017'
					AND COALESCE(a.STATUS, 0) = '1'
			) AS c ON a.KD_UNIT_ORG_RANGKING = c.KD_UNIT_ORG AND COALESCE(a.kd_konversi_pegawai_tertentu, '0') = c.kd_konversi_pegawai_tertentu
			INNER JOIN
			(
				SELECT  
					MAX(JENIS_KELOMPOK) AS JENIS_KELOMPOK, 
					NIP, 
					PFM_ID
				FROM  
					PFM_RANGKING_JABATAN
				GROUP BY 
					NIP, 
					PFM_ID
			) AS aktif ON a.PFM_ID = aktif.PFM_ID AND a.NIP = aktif.NIP AND a.JENIS_KELOMPOK = aktif.JENIS_KELOMPOK
		WHERE
			a.PFM_ID = 'PFM-000017'
			--AND a.kd_konversi_pegawai_tertentu = '1'
			AND COALESCE(a.PFN_ID, '') <> ''
	) AS lima ON sdc.NIP = lima.NIP
WHERE
	YEAR(COALESCE(sdc.TGL_BERHENTI, GETDATE())) >= 2018
ORDER BY
	sdc.NIP


--select * from pfm_periode

--SELECT TOP 200 * FROM SPG_DATA_CURRENT WHERE STS_PENSIUN = '1' AND LEFT(NIP, 1) = '0' ORDER BY ID DESC
--SELECT TOP 200 * FROM SPG_DATA_CURRENT WHERE TGL_BERHENTI IS NULL

--1: 2022
--2: 2021
--3: 2020
--4: 2019
--5: 2018
