-- pakai tanggal
DECLARE 
	@dari_tanggal INT = 20200801,			-- yyyymmdd, contoh 20201221 (untuk 21 Des 2020)
	@sampai_tanggal INT = 20200830		-- yyyymmdd, contoh 20201231 (untuk 31 Des 2020)
	

-- sebelumnya pakai tahun
--DECLARE @tahun VARCHAR(4) = '2020'


-- cukup ubah yang diatas ini aja
----------------------------------------------------------------------------------------------

SELECT
	sdc.NIP,
	sdc.NM_PEG,
	COALESCE(bak.JML_BAK, 0) AS JML_BAK,
	COALESCE(l.JML_L, 0) AS JML_L,
	COALESCE(tp.JML_TP, 0) AS JML_TP,
	COALESCE(ll.JML_LL, 0) AS JML_LL
FROM
	SPG_DATA_CURRENT AS sdc WITH (NOLOCK) OUTER APPLY
	(
		SELECT
			sra1.NIP,
			LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4) AS TAHUN,
			COUNT(*) AS JML_BAK
		FROM 
			SPG_REPORT_ABSENSI AS sra1 WITH (NOLOCK)
		WHERE 
			(
				sra1.TIPE LIKE '9|%' 
				OR sra1.TIPE LIKE '%|9' 
				OR sra1.TIPE LIKE '33|%' 
				OR sra1.TIPE LIKE '%|33' 
				OR sra1.TIPE = '60'
			)
			AND sra1.NIP = sdc.NIP
			--AND LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4) = @tahun
			AND sra1.TANGGAL_ABSEN >= @dari_tanggal
			AND sra1.TANGGAL_ABSEN <= @sampai_tanggal
		GROUP BY
			sra1.NIP,
			LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4)
	) AS bak OUTER APPLY
	(
		SELECT
			sra2.NIP,
			LEFT(CAST(sra2.TANGGAL_ABSEN AS VARCHAR(8)),4) AS TAHUN,
			COUNT(*) AS JML_L
		FROM 
			SPG_REPORT_ABSENSI AS sra2 WITH (NOLOCK) LEFT JOIN
			(
				SELECT
					ID
				FROM 
					SPG_REPORT_ABSENSI WITH (NOLOCK)
				WHERE 
					TIPE LIKE '9|%' 
					OR TIPE LIKE '%|9' 
					OR TIPE LIKE '33|%' 
					OR TIPE LIKE '%|33' 
					OR TIPE = '60'
			) AS exc_bak ON sra2.ID = exc_bak.ID
		WHERE 
			(
				sra2.TIPE LIKE '11|%' 
				OR sra2.TIPE LIKE '%|11' 
				OR sra2.TIPE LIKE '11|%' 
				OR sra2.TIPE LIKE '%|11' 
				OR sra2.TIPE = '11'
			)
			AND exc_bak.ID IS NULL
			AND sra2.NIP = sdc.NIP
			--AND LEFT(CAST(sra2.TANGGAL_ABSEN AS VARCHAR(8)),4) = @tahun
			AND sra2.TANGGAL_ABSEN >= @dari_tanggal
			AND sra2.TANGGAL_ABSEN <= @sampai_tanggal
		GROUP BY
			sra2.NIP,
			LEFT(CAST(sra2.TANGGAL_ABSEN AS VARCHAR(8)),4)
	) AS l OUTER APPLY
	(
		SELECT
			sra3.NIP,
			LEFT(CAST(sra3.TANGGAL_ABSEN AS VARCHAR(8)),4) AS TAHUN,
			COUNT(*) AS JML_TP
		FROM 
			SPG_REPORT_ABSENSI AS sra3 WITH (NOLOCK) LEFT JOIN
			(
				SELECT
					ID
				FROM 
					SPG_REPORT_ABSENSI WITH (NOLOCK)
				WHERE 
					TIPE LIKE '9|%' 
					OR TIPE LIKE '%|9' 
					OR TIPE LIKE '33|%' 
					OR TIPE LIKE '%|33' 
					OR TIPE = '60'
			) AS exc_bak ON sra3.ID = exc_bak.ID
		WHERE 
			(
				sra3.TIPE LIKE '18|%' 
				OR sra3.TIPE LIKE '%|18' 
				OR sra3.TIPE LIKE '18|%' 
				OR sra3.TIPE LIKE '%|18' 
				OR sra3.TIPE = '18'
			)
			AND exc_bak.ID IS NULL
			AND sra3.NIP = sdc.NIP
			--AND LEFT(CAST(sra3.TANGGAL_ABSEN AS VARCHAR(8)),4) = @tahun
			AND sra3.TANGGAL_ABSEN >= @dari_tanggal
			AND sra3.TANGGAL_ABSEN <= @sampai_tanggal
		GROUP BY
			sra3.NIP,
			LEFT(CAST(sra3.TANGGAL_ABSEN AS VARCHAR(8)),4)
	) AS tp OUTER APPLY
	(
		SELECT
			sra1.NIP,
			LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4) AS TAHUN,
			COUNT(*) AS JML_LL
		FROM 
			SPG_REPORT_ABSENSI AS sra1 WITH (NOLOCK)
		WHERE 
			sra1.TIPE = '57' 
			AND sra1.NIP = sdc.NIP
			--AND LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4) = @tahun
			AND sra1.TANGGAL_ABSEN >= @dari_tanggal
			AND sra1.TANGGAL_ABSEN <= @sampai_tanggal
		GROUP BY
			sra1.NIP,
			LEFT(CAST(sra1.TANGGAL_ABSEN AS VARCHAR(8)),4)
	) AS ll 
WHERE
	sdc.STS_PENSIUN = '0'
ORDER BY
	sdc.NIP