USE [HRIS]

DECLARE @tanggal_dari DATE = '2018-01-01' -- tanggal mulai
DECLARE @tanggal_sampai DATE = '2021-12-31' -- tanggal mulai
DECLARE @tahun INT
DECLARE @bulan INT
DECLARE @hasil TABLE (tahun INT, bulan INT, jml_hari_kerja INT, jml_pegawai_lakilaki INT, jml_pegawai_perempuan INT, jml_pegawai INT, jml_hadir_lakilaki INT, jml_hadir_perempuan INT, jml_hadir INT, jml_sakit_lakilaki INT, jml_sakit_perempuan INT, jml_sakit INT, jml_cuti_lakilaki INT, jml_cuti_perempuan INT, jml_cuti INT, jml_terlambat_lakilaki INT, jml_terlambat_perempuan INT, jml_terlambat INT)

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

	INSERT INTO @hasil (tahun, bulan, jml_hari_kerja, jml_pegawai_lakilaki, jml_pegawai_perempuan, jml_pegawai, jml_hadir_lakilaki, jml_hadir_perempuan, jml_hadir, jml_sakit_lakilaki, jml_sakit_perempuan, jml_sakit, jml_cuti_lakilaki, jml_cuti_perempuan, jml_cuti, jml_terlambat_lakilaki, jml_terlambat_perempuan, jml_terlambat)
	SELECT 
		@tahun, 
		@bulan,
		(DATEDIFF(dd, DATEADD(month, DATEDIFF(month, 0, @tanggal_dari), 0), EOMONTH(@tanggal_dari)) + 1)
		-(DATEDIFF(wk, DATEADD(month, DATEDIFF(month, 0, @tanggal_dari), 0), EOMONTH(@tanggal_dari)) * 2)
		-(CASE WHEN DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, @tanggal_dari), 0)) = 'Sunday' THEN 1 ELSE 0 END)
		-(CASE WHEN DATENAME(dw, EOMONTH(@tanggal_dari)) = 'Saturday' THEN 1 ELSE 0 END)
		-(SELECT COUNT(*) FROM dbo.SPG_HARI_LIBUR WHERE TANGGAL_HARI_LIBUR BETWEEN DATEADD(month, DATEDIFF(month, 0, @tanggal_dari), 0) AND EOMONTH(@tanggal_dari) AND NASIONAL = '1'),
		peg.jml_lakilaki,
		peg.jml_perempuan,
		peg.jml,
		hadir.jml_hadir_lakilaki,
		hadir.jml_hadir_perempuan,
		hadir.jml_hadir,
		sakit.jml_sakit_lakilaki, 
		sakit.jml_sakit_perempuan, 
		sakit.jml_sakit,
		cuti.jml_cuti_lakilaki, 
		cuti.jml_cuti_perempuan,
		cuti.jml_cuti,
		telat.jml_terlambat_lakilaki, 
		telat.jml_terlambat_perempuan,
		telat.jml_terlambat
	FROM
		(
			SELECT
				@tahun AS tahun,
				@bulan AS bulan,
				SUM(CASE WHEN p.JNS_KELAMIN_PEG = 1 THEN 1 ELSE 0 END) AS jml_lakilaki,
				SUM(CASE WHEN p.JNS_KELAMIN_PEG = 2 THEN 1 ELSE 0 END) AS jml_perempuan,
				COUNT(*) AS jml
			FROM
				(
					SELECT
						sdc.NIP,
						sdc.NM_PEG,
						sdc.JNS_KELAMIN_PEG,
						d.Dari,
						--s.Sampai,
						--CASE
						--	WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
						--	ELSE s.Sampai
						--END AS Sampai,
						--COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE)) AS Sampai,
						--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 AS Sampai,
						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
			WHERE
				p.Dari <= EOMONTH(@tanggal_dari)
				AND p.Sampai >= @tanggal_dari
		) AS peg OUTER APPLY
		(
			SELECT
				SUM(CASE WHEN ph.JNS_KELAMIN_PEG = 1 THEN 1 ELSE 0 END) AS jml_hadir_lakilaki,
				SUM(CASE WHEN ph.JNS_KELAMIN_PEG = 2 THEN 1 ELSE 0 END) AS jml_hadir_perempuan,
				COUNT(*) AS jml_hadir
			FROM
				(
					SELECT
						--COUNT( DISTINCT sra.NIP ) AS jml_hadir
						--COUNT(DISTINCT sra.ID ) AS jml_hadir,
						DISTINCT
						--sra.ID,
						sra.NIP,
						sra.TANGGAL_ABSEN,
						sdc.JNS_KELAMIN_PEG
					FROM
						SPG_REPORT_ABSENSI AS sra --INNER JOIN
						--SPG_JNS_ABSENSI AS sja ON sra.TIPE = sja.ID
						INNER JOIN
						SPG_DATA_CURRENT AS sdc ON sra.NIP = sdc.NIP
					WHERE
						--sja.KEHADIRAN = 1
						(
							sra.TIPE = '1'
							OR sra.TIPE LIKE '%11%'
							OR sra.TIPE LIKE '%18%'
							OR sra.TIPE LIKE '%53%'
							OR sra.TIPE LIKE '%54%'
							OR sra.TIPE LIKE '%55%'
							OR sra.TIPE LIKE '%56%'
							OR sra.TIPE LIKE '%57%'
							OR sra.TIPE LIKE '%58%'
							OR sra.TIPE LIKE '%59%'
							OR sra.TIPE LIKE '%45%'	-- Dinas
							OR sra.TIPE LIKE '%46%'	-- Dinas_perjalanan
						)
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) <= EOMONTH(@tanggal_dari)
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= @tanggal_dari
						AND sra.NIP IN (
									SELECT
										p.NIP
									FROM
										(
											SELECT
												sdc.NIP,
												sdc.NM_PEG,
												d.Dari,
												--s.Sampai,
												--CASE
												--	WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
												--	ELSE s.Sampai
												--END AS Sampai,
												--COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE)) AS Sampai,
												--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 AS Sampai,
												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
									WHERE
										p.Dari <= EOMONTH(@tanggal_dari)
										AND p.Sampai >= @tanggal_dari
								)
				) AS ph
		) AS hadir OUTER APPLY
		(
			SELECT
				SUM(CASE WHEN ps.JNS_KELAMIN_PEG = 1 THEN 1 ELSE 0 END) AS jml_sakit_lakilaki,
				SUM(CASE WHEN ps.JNS_KELAMIN_PEG = 2 THEN 1 ELSE 0 END) AS jml_sakit_perempuan,
				COUNT(*) AS jml_sakit
			FROM
				(
					SELECT
						--COUNT( DISTINCT sra.NIP ) AS jml_sakit
						--COUNT( DISTINCT sra.ID ) AS jml_sakit
						DISTINCT
						--sra.ID,
						sra.NIP,
						sra.TANGGAL_ABSEN,
						sdc.JNS_KELAMIN_PEG
					FROM
						SPG_REPORT_ABSENSI AS sra INNER JOIN
						SPG_JNS_ABSENSI AS sja ON sra.TIPE = sja.ID INNER JOIN
						SPG_DATA_CURRENT AS sdc ON sra.NIP = sdc.NIP
					WHERE
						TIPE IN (51, 52)
						AND TIPE NOT LIKE '%|%'
						AND TIPE NOT LIKE '% %'
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) <= EOMONTH(@tanggal_dari)
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= @tanggal_dari
						AND sra.NIP IN (
									SELECT
										p.NIP
									FROM
										(
										SELECT
												sdc.NIP,
												sdc.NM_PEG,
												d.Dari,
												--s.Sampai,
												--CASE
												--	WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
												--	ELSE s.Sampai
												--END AS Sampai,
												--COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE)) AS Sampai,
												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
									WHERE
										p.Dari <= EOMONTH(@tanggal_dari)
										AND p.Sampai >= @tanggal_dari
								)
				) ps
		) AS sakit OUTER APPLY
		(
			SELECT
				SUM(CASE WHEN pc.JNS_KELAMIN_PEG = 1 THEN 1 ELSE 0 END) AS jml_cuti_lakilaki,
				SUM(CASE WHEN pc.JNS_KELAMIN_PEG = 2 THEN 1 ELSE 0 END) AS jml_cuti_perempuan,
				COUNT(*) AS jml_cuti
			FROM
				(

					SELECT
						--COUNT( DISTINCT sra.NIP ) AS jml_cuti
						--COUNT( DISTINCT sra.ID ) AS jml_cuti
						DISTINCT
						--sra.ID,
						sra.NIP,
						sra.TANGGAL_ABSEN,
						sdc.JNS_KELAMIN_PEG
					FROM
						SPG_REPORT_ABSENSI AS sra INNER JOIN
						SPG_JNS_ABSENSI AS sja ON sra.TIPE = sja.ID INNER JOIN
						SPG_DATA_CURRENT AS sdc ON sra.NIP = sdc.NIP
					WHERE
						TIPE IN (41, 7, 63, 43, 62, 38, 39, 40, 41, 44, 42)
						AND TIPE NOT LIKE '%|%'
						AND TIPE NOT LIKE '% %'
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) <= EOMONTH(@tanggal_dari)
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= @tanggal_dari
						AND sra.NIP IN (
									SELECT
										p.NIP
									FROM
										(
										SELECT
												sdc.NIP,
												sdc.NM_PEG,
												d.Dari,
												--s.Sampai,
												--CASE
												--	WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
												--	ELSE s.Sampai
												--END AS Sampai,
												--COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE)) AS Sampai,
												--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 AS Sampai,
												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
									WHERE
										p.Dari <= EOMONTH(@tanggal_dari)
										AND p.Sampai >= @tanggal_dari
								)
				) AS pc
		) AS cuti OUTER APPLY
		(
			SELECT
				SUM(CASE WHEN pt.JNS_KELAMIN_PEG = 1 THEN 1 ELSE 0 END) AS jml_terlambat_lakilaki,
				SUM(CASE WHEN pt.JNS_KELAMIN_PEG = 2 THEN 1 ELSE 0 END) AS jml_terlambat_perempuan,
				COUNT(*) AS jml_terlambat
			FROM
				(
					SELECT
						--COUNT( DISTINCT sra.NIP ) AS jml_terlambat
						--COUNT( DISTINCT sra.ID ) AS jml_terlambat
						DISTINCT
						--sra.ID,
						sra.NIP,
						sra.TANGGAL_ABSEN,
						sdc.JNS_KELAMIN_PEG
					FROM
						SPG_REPORT_ABSENSI AS sra INNER JOIN
						SPG_JNS_ABSENSI AS sja ON sra.TIPE = sja.ID INNER JOIN
						SPG_DATA_CURRENT AS sdc ON sra.NIP = sdc.NIP
					WHERE
						TIPE IN (11, 18, 55, 56)
						AND TIPE NOT LIKE '%|%'
						AND TIPE NOT LIKE '% %'
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) <= EOMONTH(@tanggal_dari)
						AND CAST(CAST(sra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= @tanggal_dari
						AND sra.NIP IN (
									SELECT
										p.NIP
									FROM
										(
										SELECT
												sdc.NIP,
												sdc.NM_PEG,
												d.Dari,
												--s.Sampai,
												--CASE
												--	WHEN sdc.STS_PENSIUN = '0' THEN CAST(DATEADD(day, 1, GETDATE()) AS DATE)
												--	ELSE s.Sampai
												--END AS Sampai,
												--COALESCE(s.Sampai, CAST(DATEADD(day, 1, GETDATE()) AS DATE)) AS Sampai,
												--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 AS Sampai,
												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
									WHERE
										p.Dari <= EOMONTH(@tanggal_dari)
										AND p.Sampai >= @tanggal_dari
								)
				) AS pt
		) AS telat

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

SELECT * FROM @hasil