DECLARE @hasil TABLE ( 
					nip VARCHAR(8), 
					nama VARCHAR(50), 
					sts_pensiun VARCHAR(20), 
					jabatan VARCHAR(100), 
					level_jabatan VARCHAR(50),
					satker VARCHAR(400),
					masa_dinas_level_jabatan VARCHAR(100), 
					sisa_masa_dinas_ojk VARCHAR(100), 
					KD_JABATAN_STR varchar(10),
					nilai_2021 FLOAT,
					level_kinerja_2021 INT,
					nilai_2020 FLOAT,
					level_kinerja_2020 INT,
					nilai_2019 FLOAT,
					level_kinerja_2019 INT,
					nilai_2018 FLOAT,
					level_kinerja_2018 INT,
					nilai_2017 FLOAT,
					level_kinerja_2017 INT
				)
INSERT INTO @hasil
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.NM_JABATAN AS JABATAN,
	gol.NM_GOLONGAN AS [LEVEL JABATAN],
	suo.nama_satker_organisasi,
	CASE 
		WHEN p.lama_clto_latest_lvl_jab > 0 THEN dbo.MasaDinasCLTO( CASE
															WHEN sdc.STS_PENSIUN = '0' THEN dbo.MasaDinasTahunBulanDataMart(sdc.TMT_GOL, getdate())
															WHEN sdc.STS_PENSIUN = '1' THEN dbo.MasaDinasTahunBulanDataMart(sdc.TMT_GOL, sdc.TGL_BERHENTI)
														END, p.lama_clto_latest_lvl_jab, '-')
		ELSE CASE
				WHEN sdc.STS_PENSIUN = '0' THEN dbo.MasaDinasTahunBulanDataMart(sdc.TMT_GOL,  getdate())
				WHEN sdc.STS_PENSIUN = '1' THEN dbo.MasaDinasTahunBulanDataMart(sdc.TMT_GOL, sdc.TGL_BERHENTI)
			END
	END AS masa_dinas_level_jabatan,
     CASE WHEN q.lama_clto > 0
          THEN dbo.MasaDinasCLTO(CASE
     WHEN sdc.STS_PENSIUN = '0' THEN 
          CASE 
          WHEN sdc.KD_STATUS_KEPEG = '200' THEN HRIS.dbo.MasaDinasTahunBulanDataMart(GETDATE(), sdc.TGL_SELESAI_KONTRAK) 
          ELSE dbo.MasaDinasTahunBulanDataMart(GETDATE(), sdc.TMT_PENSIUN) 
          END 
     WHEN sdc.STS_PENSIUN = '1' THEN HRIS.dbo.MasaDinasTahunBulanDataMart(sdc.TGL_BERHENTI, sdc.TGL_BERHENTI) 
     END, q.lama_clto, '+')
          ELSE CASE
     WHEN sdc.STS_PENSIUN = '0' THEN 
          CASE 
          WHEN sdc.KD_STATUS_KEPEG = '200' THEN HRIS.dbo.MasaDinasTahunBulanDataMart(GETDATE(), sdc.TGL_SELESAI_KONTRAK) 
          ELSE dbo.MasaDinasTahunBulanDataMart(GETDATE(), sdc.TMT_PENSIUN) 
          END 
     WHEN sdc.STS_PENSIUN = '1' THEN HRIS.dbo.MasaDinasTahunBulanDataMart(sdc.TGL_BERHENTI, sdc.TGL_BERHENTI) 
     END
     END AS SISA_MASA_DINAS_OJK,
	sdc.KD_JABATAN_STR,
	NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
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
	SPG_GOLONGAN AS gol ON sdc.KD_GOL = gol.KD_GOL LEFT JOIN 
	(
            select a.nip,
            ISNULL(SUM((DATEDIFF(DAY, b.TGL_AWAL_CUTI_PEG, b.TGL_AKHIR_CUTI_PEG)/30)), 0) as lama_clto_latest_lvl_jab
            from SPG_DATA_CURRENT a
            left join SPG_CUTI_PEGAWAI b on a.nip=b.nip
            where b.JNS_CUTI='16' and b.TGL_AWAL_CUTI_PEG >= a.TMT_GOL and b.TGL_AKHIR_CUTI_PEG >= a.TMT_GOL
            group by a.nip
      ) p on sdc.nip=p.nip LEFT JOIN 
	   (
            select nip, ISNULL(SUM((DATEDIFF(DAY, TGL_AWAL_CUTI_PEG, TGL_AKHIR_CUTI_PEG)/30)), 0) as lama_clto from SPG_CUTI_PEGAWAI where JNS_CUTI='16'
            group by nip
          ) q on sdc.nip=q.nip
WHERE
	LEFT(sdc.NIP, 1) = '0'
	AND YEAR(COALESCE(sdc.TGL_BERHENTI, GETDATE())) >= 2017
	AND suo.nama_satker_organisasi = 'Grup Pengembangan Aplikasi Sistem Informasi'
ORDER BY
	sdc.KD_JABATAN_STR,
	sdc.NIP

DECLARE @nip VARCHAR(8)

DECLARE list_cursor CURSOR FOR
SELECT nip FROM @hasil

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @nip  

WHILE @@FETCH_STATUS = 0  
BEGIN 

	UPDATE
		h
	SET
		nilai_2021 = prk.NILAI,
		level_kinerja_2021 = prk.LEVEL_KINERJA
	FROM
		@hasil AS h INNER JOIN
		PFM_RIWAYAT_KINERJA AS prk ON h.NIP = prk.NIP
	WHERE
		h.nip = @nip
		AND YEAR(prk.TANGGAL_MULAI) = 2021
		AND COALESCE(prk.KD_STATUS_VALIDASI, '0') = 1

	UPDATE
		h
	SET
		nilai_2020 = prk.NILAI,
		level_kinerja_2020 = prk.LEVEL_KINERJA
	FROM
		@hasil AS h INNER JOIN
		PFM_RIWAYAT_KINERJA AS prk ON h.NIP = prk.NIP
	WHERE
		h.nip = @nip
		AND YEAR(prk.TANGGAL_MULAI) = 2020
		AND COALESCE(prk.KD_STATUS_VALIDASI, '0') = 1

	UPDATE
		h
	SET
		nilai_2019 = prk.NILAI,
		level_kinerja_2019 = prk.LEVEL_KINERJA
	FROM
		@hasil AS h INNER JOIN
		PFM_RIWAYAT_KINERJA AS prk ON h.NIP = prk.NIP
	WHERE
		h.nip = @nip
		AND YEAR(prk.TANGGAL_MULAI) = 2019
		AND COALESCE(prk.KD_STATUS_VALIDASI, '0') = 1

	UPDATE
		h
	SET
		nilai_2018 = prk.NILAI,
		level_kinerja_2018 = prk.LEVEL_KINERJA
	FROM
		@hasil AS h INNER JOIN
		PFM_RIWAYAT_KINERJA AS prk ON h.NIP = prk.NIP
	WHERE
		h.nip = @nip
		AND YEAR(prk.TANGGAL_MULAI) = 2018
		AND COALESCE(prk.KD_STATUS_VALIDASI, '0') = 1

	UPDATE
		h
	SET
		nilai_2017 = prk.NILAI,
		level_kinerja_2017 = prk.LEVEL_KINERJA
	FROM
		@hasil AS h INNER JOIN
		PFM_RIWAYAT_KINERJA AS prk ON h.NIP = prk.NIP
	WHERE
		h.nip = @nip
		AND YEAR(prk.TANGGAL_MULAI) = 2017
		AND COALESCE(prk.KD_STATUS_VALIDASI, '0') = 1


	FETCH NEXT FROM list_cursor INTO @nip  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT 
	*
FROM 
	@hasil
--WHERE
--	nilai_2021 IS NOT NULL
--	OR nilai_2020 IS NOT NULL
--	OR nilai_2019 IS NOT NULL
--	OR nilai_2018 IS NOT NULL
--	OR nilai_2017 IS NOT NULL
ORDER BY
	KD_JABATAN_STR,
	NIP