SELECT
	sdc.NIP,
	sdc.NM_PEG,
	sdc.KD_UNIT_ORG,
	suo.nama_satker_organisasi,
	COALESCE(b.[Persentase EPresensi], 0) AS [Persentase EPresensi]
FROM
	SPG_DATA_CURRENT AS sdc LEFT JOIN
	(
		SELECT
			a.NIP,
			SUM(a.penuh) * 100 / COUNT(*) AS [Persentase EPresensi]
		FROM
			(
				SELECT
					NIP,
					TANGGAL_ABSEN,
					CASE
						WHEN MOBILE_MASUK = 1 AND MOBILE_KELUAR = 1 THEN 1
						ELSE 0
					END AS penuh
				FROM
					SPG_REPORT_ABSENSI
				WHERE
					TIPE NOT IN ('5', '7', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '61', '62', '63', '64')
					AND CAST(CAST(TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) > EOMONTH(DATEADD(MONTH, -6, GETDATE()))	-- 6 bulan kebelakang
			) AS a
		GROUP BY
			NIP
	) AS b ON sdc.NIP = b.NIP LEFT JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG
WHERE
	sdc.STS_PENSIUN = '0'
ORDER BY
	sdc.KD_UNIT_ORG,
	sdc.NIP