--INSERT INTO HRIS.[dbo].[SPG_ABSENSI_DUMP]
--           ([TERM_ID]
--           ,[FP_ID]
--           ,[T_DATE]
--           ,[FKEY]
--           ,[REF_ID]
--           ,[CREATTE_DATE]
--           ,[ID_LOGS])
--SELECT replace(term_id, ',', '.')
--      ,[FP_ID]
--      ,DATEADD(hour, -5, [T_DATE])
--      ,[FKEY]
--      ,[REF_ID]
--      ,GETDATE()
--      ,[ID_LOGS]
--  FROM [HRIS].[dbo].[SPG_ABSENSI_DUMP]
--WHERE
--	CREATTE_DATE >= '2023-08-14 08:28:00'
--	AND SUBSTRING(term_id, 4, 1) = ','

SELECT *
  FROM [HRIS].[dbo].[SPG_ABSENSI_DUMP]
WHERE
	CREATTE_DATE >= '2023-08-14 08:28:00'
	AND SUBSTRING(term_id, 4, 1) = ','
ORDER BY CREATTE_DATE 

SELECT * FROM SPG_ABSENSI_DUMP WHERE RIGHT(fp_id, 5) = '02962' AND T_DATE >= '2023-08-14'
SELECT * FROM SPG_ABSENSI WHERE NIP = '02962' AND TANGGAL_ABSEN = '20230814'

SELECT * FROM SPG_REPORT_ABSENSI WHERE NIP = '02962' AND TANGGAL_ABSEN = 20230814

SELECT DATEADD(hour, -5, KELUAR), replace(KELUAR_TERM_ID, ',', '.'), * FROM SPG_ABSENSI WHERE TANGGAL_ABSEN = '20230814' AND MASUK IS NULL
--UPDATE
--	SPG_ABSENSI
--SET
--	MASUK = DATEADD(hour, -5, KELUAR),
--	MASUK_TERM_ID = replace(KELUAR_TERM_ID, ',', '.')
--WHERE 
--	TANGGAL_ABSEN = '20230814' 
--	AND MASUK IS NULL
--UPDATE
--	SPG_ABSENSI
--SET
--	KELUAR = NULL,
--	KELUAR_TERM_ID = NULL
--WHERE 
--	TANGGAL_ABSEN = '20230814' 
--	AND MASUK IS NOT NULL
--	AND KELUAR IS NOT NULL

SELECT DATEADD(hour, -5, KELUAR), replace(KELUAR_TERM_ID, ',', '.'), * FROM SPG_ABSENSI WHERE TANGGAL_ABSEN = '20230814' AND MASUK_TERM_ID LIKE '%,%' --OR KELUAR_TERM_ID LIKE '%,%')
--UPDATE
--	SPG_ABSENSI
--SET
--	MASUK = DATEADD(hour, -5, MASUK),
--	MASUK_TERM_ID = replace(MASUK_TERM_ID, ',', '.'),
--	KELUAR = NULL,
--	KELUAR_TERM_ID = NULL
--WHERE 
--	TANGGAL_ABSEN = '20230814' 
--	AND (MASUK_TERM_ID LIKE '%,%')
--	AND NIP <> '01522'

SELECT * FROM SPG_ABSENSI WHERE TANGGAL_ABSEN = '20230814' AND NIP = '01522'
--UPDATE SPG_ABSENSI SET KELUAR = NULL, KELUAR_TERM_ID = NULL WHERE TANGGAL_ABSEN = '20230814' AND NIP = '01522'

SELECT * FROM SPG_ABSENSI_DUMP WHERE T_DATE >= '2023-08-14 00:00:00' AND CREATTE_DATE <= '2023-08-14 09:00:00'  AND CREATTE_DATE >= '2023-08-14 00:00:00' ORDER BY FP_ID


SELECT * FROM SPG_ABSENSI_DUMP WHERE RIGHT(fp_id, 5) = '01163' AND T_DATE >= '2023-08-14'
SELECT * FROM SPG_ABSENSI WHERE NIP = '03521' AND TANGGAL_ABSEN = '20230814'

SELECT * FROM SPG_REPORT_ABSENSI WHERE NIP = '03367' AND TANGGAL_ABSEN = 20230814


SELECT * FROM SPG_REPORT_ABSENSI WHERE TANGGAL_ABSEN = 20230814 AND COALESCE(JAM_KELUAR,'') <> ''

SELECT * FROM SPG_REPORT_ABSENSI WHERE TANGGAL_ABSEN = 20230814 AND JAM_MASUK >= '090000'

SELECT * FROM SPG_JNS_ABSENSI

SELECT * FROM SPG_REPORT_ABSENSI WHERE TANGGAL_ABSEN = 20230814 AND (
		MOBILE_MASUK_LOC LIKE '%,%'
		OR MOBILE_KELUAR_LOC LIKE '%,%'
		)
SELECT * FROM SPG_REPORT_ABSENSI WHERE TANGGAL_ABSEN = 20230814 AND NIP IN ('04494','01522')