DECLARE 
	@nip VARCHAR(max) = '01728, 01143',	-- isi dengan NIP, jika NIP lebih dari satu, gunakan tanda koma sebagai pemisah
	@kd_unit_org VARCHAR(max) = '',	-- isi dengan KD_UNIT_ORG, jika KD_UNIT_ORG lebih dari satu, gunakan tanda koma sebagai pemisah
	@tanggal_dari DATE = '2021-12-01',	--'2021-08-01', -- tanggal mulai
	@tanggal_sampai DATE = '2021-12-31'	--'2021-10-31' -- tanggal mulai


--IF OBJECT_ID(N'tempdb..#hasil') IS NOT NULL
--BEGIN
--	DROP TABLE #hasil
--END

--CREATE TABLE #hasil
DECLARE 
	@hasil TABLE
	(
		TAHUN            SMALLINT, 
		BULAN            TINYINT, 
		NIP              VARCHAR(8), 
		NAMA             VARCHAR(100), 
		SATKER           VARCHAR(300), 
		SINGKATAN_SATKER VARCHAR(50), 
		K                SMALLINT DEFAULT 0, 
		A                SMALLINT DEFAULT 0, 
		BAK              SMALLINT DEFAULT 0, 
		CB               SMALLINT DEFAULT 0, 
		CM               SMALLINT DEFAULT 0, 
		CMP              SMALLINT DEFAULT 0, 
		CS               SMALLINT DEFAULT 0, 
		CT               SMALLINT DEFAULT 0, 
		CX               SMALLINT DEFAULT 0, 
		CKIK             SMALLINT DEFAULT 0, 
		D                SMALLINT DEFAULT 0, 
		DP               SMALLINT DEFAULT 0, 
		DS               SMALLINT DEFAULT 0, 
		L                SMALLINT DEFAULT 0, 
		PN               SMALLINT DEFAULT 0, 
		PS               SMALLINT DEFAULT 0, 
		PT               SMALLINT DEFAULT 0, 
		S                SMALLINT DEFAULT 0, 
		SD               SMALLINT DEFAULT 0, 
		LL               SMALLINT DEFAULT 0, 
		AM               SMALLINT DEFAULT 0, 
		KM               SMALLINT DEFAULT 0, 
		CLT              SMALLINT DEFAULT 0, 
		CBR              SMALLINT DEFAULT 0, 
		DSL              SMALLINT DEFAULT 0, 
		TP               SMALLINT DEFAULT 0, 
		LBR              SMALLINT DEFAULT 0, 
		MNT_C            SMALLINT DEFAULT 0, 
		MNT_L            SMALLINT DEFAULT 0
	)

DECLARE
	@tahun INT,
	@bulan INT

WHILE @tanggal_dari <= @tanggal_sampai
BEGIN
	SET @tahun = YEAR(@tanggal_dari)
	SET @bulan = MONTH(@tanggal_dari)

	INSERT INTO @hasil (TAHUN, BULAN, NIP, NAMA, SATKER, SINGKATAN_SATKER)
	SELECT
		@tahun,
		@bulan,
		p.NIP,
		p.NM_PEG,
		r.nama_satker_organisasi,
		r.SINGKATAN_UNIT_ORG
	FROM
		(
			SELECT
				sdc.NIP,
				sdc.NM_PEG,
				sdc.JNS_KELAMIN_PEG,
				d.Dari,
				CASE
					WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
					ELSE
						CASE
							WHEN s.Sampai IS NOT NULL AND s.Sampai <= d.Dari THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
							ELSE COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE))
						END
				END AS Sampai,
				sdc.STS_PENSIUN
			FROM
				SPG_DATA_CURRENT AS sdc LEFT JOIN
				(
					SELECT LTRIM(RTRIM(NIP)) AS NIP, MAX(TMT_PENSIUN) AS Sampai FROM spg_pegawai_pensiun WHERE KD_STATUS_VALIDASI = 1 AND STS_PENSIUN = '1' AND JNS_STS_KEAKTIFAN = 1 GROUP BY LTRIM(RTRIM(NIP))
				) AS s ON sdc.NIP = s. NIP LEFT JOIN
				(
					SELECT LTRIM(RTRIM(NIP)) AS NIP, MIN(TMT_PENSIUN) AS Dari FROM spg_pegawai_pensiun WHERE KD_STATUS_VALIDASI = 1 AND STS_PENSIUN = '0' GROUP BY LTRIM(RTRIM(NIP))
				) AS d ON sdc.NIP = d. NIP
		) AS p
		LEFT JOIN
		(
			SELECT
				*
			FROM
				(
					SELECT
						riwayat.NIP,
						riwayat.NM_PEG,
						riwayat.NM_UNIT_ORG,
						riwayat.nama_satker_organisasi,
						riwayat.SINGKATAN_UNIT_ORG,
						MIN(riwayat.TANGGAL_RIWAYAT) AS Dari,
						MAX(riwayat.NEXT_TANGGAL_RIWAYAT) AS Sampai
					FROM
						(
							SELECT DISTINCT
								srj2.NIP
							FROM
								SPG_08_RIWAYAT_JABATAN AS srj2 INNER JOIN
								SPG_08_UNIT_ORGANISASI AS suo2 ON srj2.KD_UNIT_ORG = suo2.KD_UNIT_ORG
							WHERE
								srj2.KD_STATUS_VALIDASI = 1
						) AS pernah CROSS APPLY
						(
							SELECT 
								srj.NIP,
								sdc.NIP_LAMA,
								sdc.NM_PEG,
								sdc.JNS_KELAMIN_PEG,
								srj.NO_URUT_JAB,
								srj.TANGGAL_RIWAYAT,
								CASE
									WHEN LEAD(srj.TANGGAL_RIWAYAT,1) OVER (ORDER BY srj.NO_URUT_JAB) IS NULL AND EXISTS (SELECT 1 FROM SPG_DATA_CURRENT AS sdc2 WHERE sdc2.NIP_LAMA = sdc.NIP) THEN 
										DATEADD(DAY, -1, (SELECT MIN(TANGGAL_RIWAYAT) FROM SPG_DATA_CURRENT AS sdc3 INNER JOIN SPG_08_RIWAYAT_JABATAN AS srj3 ON srj3.NIP = sdc3.NIP WHERE sdc3.NIP_LAMA = sdc.NIP))
									ELSE COALESCE(LEAD(srj.TANGGAL_RIWAYAT,1) OVER (
										ORDER BY srj.NO_URUT_JAB
									), CAST(GETDATE() AS DATE)) 
								END AS NEXT_TANGGAL_RIWAYAT,
								suo.NM_UNIT_ORG,
								suo.kode_satker_organisasi,
								suo.nama_satker_organisasi,
								suo.SINGKATAN_UNIT_ORG 
							FROM 
								SPG_DATA_CURRENT AS sdc INNER JOIN
								SPG_08_RIWAYAT_JABATAN AS srj ON sdc.NIP = srj.NIP INNER JOIN
								SPG_08_UNIT_ORGANISASI AS suo ON srj.KD_UNIT_ORG = suo.KD_UNIT_ORG
							WHERE
								srj.KD_STATUS_VALIDASI = 1
								AND sdc.NIP = pernah.NIP
						) AS riwayat
					GROUP BY
						riwayat.NIP,
						riwayat.NM_PEG,
						riwayat.NM_UNIT_ORG,
						riwayat.nama_satker_organisasi,
						riwayat.SINGKATAN_UNIT_ORG
				) AS prj
			WHERE
				prj.Dari <= EOMONTH(@tanggal_dari) 
				AND prj.Sampai > @tanggal_dari
		) AS r ON p.NIP = r.NIP
	WHERE
		p.Dari <= EOMONTH(@tanggal_dari)
		AND p.Sampai >= @tanggal_dari

	SET @tanggal_dari = DATEADD(MONTH, 1, @tanggal_dari)
END

SELECT * FROM @hasil ORDER BY TAHUN, BULAN, NAMA, NIP DESC