
SELECT       
	sdc.NIP, 
	sdc.NM_PEG, 
	sdc.tmt_lembaga_asal, 
	CASE
		WHEN sdc.tmt_lembaga_asal IS NOT NULL THEN 
			
	CAST(DATEDIFF(day, sdc.tmt_lembaga_asal,
					   CASE
						   WHEN ISNULL(sdc.sts_pensiun, 0) <> 1
						   THEN GETDATE()
						   ELSE sdc.tmt_pensiun
					   END) / 365 AS VARCHAR(3)) + ' Tahun ' + CAST(DATEDIFF(month, sdc.tmt_lembaga_asal,
																		   CASE
																			   WHEN ISNULL(sdc.sts_pensiun, 0) <> 1
																			   THEN GETDATE()
																			   ELSE sdc.tmt_pensiun
																		   END) - ((DATEDIFF(day, sdc.tmt_lembaga_asal,
																								CASE
																									WHEN ISNULL(sdc.sts_pensiun, 0) <> 1
																									THEN GETDATE()
																									ELSE sdc.tmt_pensiun
																								END) / 365) * 12) AS VARCHAR(3)) + ' Bulan' AS masa_dinas_lembaga_asal, 
	sdc.TMT_OJK,
	CASE
		WHEN q.lama_clto > 0
		THEN dbo.MasaDinasCLTO
(CASE
	 WHEN sdc.STS_PENSIUN = '0'
	 THEN dbo.MasaDinasTahunBulanDataMart(a.TGL_MASUK, GETDATE())
	 WHEN sdc.STS_PENSIUN = '1'
	 THEN dbo.MasaDinasTahunBulanDataMart(a.TGL_MASUK, sdc.TGL_BERHENTI)
 END, q.lama_clto, '-'
)
		ELSE CASE
				WHEN sdc.STS_PENSIUN = '0'
				THEN dbo.MasaDinasTahunBulanDataMart(a.TGL_MASUK, GETDATE())
				WHEN sdc.STS_PENSIUN = '1'
				THEN dbo.MasaDinasTahunBulanDataMart(a.TGL_MASUK, sdc.TGL_BERHENTI)
			END
	END AS masa_dinas_ojk_luar_tanggugan
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 
	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) AS q ON sdc.nip = q.nip
	INNER JOIN
	spg_pegawai AS a ON sdc.NIP = a.NIP
WHERE
	sdc.STS_PENSIUN = '0'
	AND suo.nama_satker_organisasi = 'Grup Pengembangan Aplikasi Sistem Informasi'
ORDER BY 
	sdc.KD_JABATAN_STR, 
	sdc.NM_PEG;

