SELECT
	sdc.NIP,
	sdc.NM_PEG,
	CAST(CAST(ra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) AS Tanggal,
	ra.JAM_MASUK,
	ra.JAM_KELUAR,
	ra.JAM_EFEKTIF,
	ra.JAM_LEMBUR,
	ra.MENIT_TERLAMBAT,
	--ra.
	CASE
		WHEN COALESCE(dispen1.ket_dispen, '') <> '' THEN dispen1.ket_dispen
		WHEN COALESCE(dispen2.no_surat, '') <> '' THEN dispen2.no_surat
		ELSE ra.LOKASI_KERJA
	END AS Keterangan,
	----COALESCE(dispen1.ket_dispen, dispen2.no_surat)
	--dispen1.ket_dispen, 
	--dispen2.no_surat,

	ra.MOBILE_MASUK_LOC,
	ra.LOKASI_KERJA,
	ra.MOBILE_MASUK,
	ra.MOBILE_KELUAR,
	ra.MOBILE_KELUAR_LOC,
	ra.TANGGAL_ABSEN,
	
	ra.TIPE_HARI,
	
	ra.TIPE,
	ra.UPDATE_DATE
FROM
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG LEFT JOIN
	SPG_REPORT_ABSENSI AS ra ON sdc.NIP = ra.NIP
	OUTER APPLY
	(
		select a.nip nip_dispensasi,tanggal_start,tanggal_end,keterangan ket_dispen from SPG_DISPENSASI a
		    inner join SPG_DATA_CURRENT b on a.NIP=b.NIP and status=1
		    and b.NIP = sdc.NIP
		    and
		    (CAST(CAST(ra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) BETWEEN a.TANGGAL_START AND a.TANGGAL_END)
	) AS dispen1
	OUTER APPLY
	(
		--select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
		--inner join spg_jns_surat_tugas c on c.jenis_st=b.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip and a.nip = sdc.NIP
		--and a.RENTANG_TANGGAL<>'' and a.STATUS=1  and d.tanggal_absen = ra.tanggal_absen and (d.tipe='45' or d.tipe='46')
		--UNION 
		--select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
		--inner join spg_jns_surat_tugas c on c.jenis_st=a.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip and a.nip = sdc.NIP
		--and a.RENTANG_TANGGAL<>'' and a.STATUS=1 and d.tanggal_absen = ra.tanggal_absen and (d.tipe='45' or d.tipe='46')
		select
			x.*
		from
			(
				select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
				inner join spg_jns_surat_tugas c on c.jenis_st=b.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip 
				and a.RENTANG_TANGGAL<>'' and a.STATUS=1 and (d.tipe='45' or d.tipe='46')
				UNION 
				select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
				inner join spg_jns_surat_tugas c on c.jenis_st=a.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip 
				and a.RENTANG_TANGGAL<>'' and a.STATUS=1 and (d.tipe='45' or d.tipe='46')
			) as x
			cross apply
			dbo.SplitStrings_CTE(x.RENTANG_TANGGAL,',') as y
		where
			x.nip_dispensasi = sdc.NIP
			and CAST(RIGHT(LEFT(y.item+'01/01/1900',10), 4) + LEFT(RIGHT(LEFT(y.item+'01/01/1900',10), 7), 2) + LEFT(LEFT(y.item+'01/01/1900',10), 2) AS INT) = x.tanggal_absen
			and x.tanggal_absen = ra.tanggal_absen
			and x.tgl_mulai >= '2023-01-01' and x.tgl_selesai < '2023-02-01'
	) AS dispen2
	OUTER APPLY
	spg_jns_absensi
WHERE
	sdc.STS_PENSIUN = '0'
	AND suo.nama_satker_organisasi = 'Departemen Pengelolaan Sistem Informasi'
	--AND CAST(CAST(ra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) = CAST(GETDATE() AS DATE)
	AND CAST(CAST(ra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) >= '2023-01-01'
	AND CAST(CAST(ra.TANGGAL_ABSEN AS VARCHAR(8)) AS DATE) < '2023-02-01'
	--AND ra.LOKASI_KERJA = 'Work From Office (WFO)'
ORDER BY
	sdc.KD_JABATAN_STR,
	sdc.NIP,
	ra.TANGGAL_ABSEN

--select * from SPG_REPORT_ABSENSI where nip = '04352'

--dispensasi
--select a.nip nip_dispensasi,tanggal_start,tanggal_end,keterangan ket_dispen from SPG_DISPENSASI a
--    inner join SPG_DATA_CURRENT b on a.NIP=b.NIP  $cond and  status=1
--    and
--    (
--      (TANGGAL_START BETWEEN '$tanggal_awal_f2' AND '$tanggal_akhir_f2')
--      OR
--      (TANGGAL_END BETWEEN '$tanggal_awal_f2' AND '$tanggal_akhir_f2')
--    )

--select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
--	inner join spg_jns_surat_tugas c on c.jenis_st=b.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip
--	$cond and a.RENTANG_TANGGAL<>'' and a.STATUS=1  and d.tanggal_absen >='$tanggal_awal_f1' and d.tanggal_absen <= '$tanggal_akhir_f1' and (d.tipe='45' or d.tipe='46')";
--	$sql_st.=" UNION ";
--	$sql_st.="select b.no_surat,a.nip nip_dispensasi,a.RENTANG_TANGGAL,a.tgl_mulai,a.tgl_selesai,d.tanggal_absen,c.id_jns_absensi from spg_surat_tugas b inner join spg_peserta_surat_tugas a on a.stu_id=b.stu_id
--	inner join spg_jns_surat_tugas c on c.jenis_st=a.type INNER JOIN SPG_REPORT_ABSENSI d on a.nip=d.nip
--	$cond and a.RENTANG_TANGGAL<>'' and a.STATUS=1  and d.tanggal_absen >='$tanggal_awal_f1' and d.tanggal_absen <= '$tanggal_akhir_f1' and (d.tipe='45' or d.tipe='46')


--select RIGHT(LEFT('05/01/2023'+'01/01/1900',10), 4) + LEFT(RIGHT(LEFT('05/01/2023'+'01/01/1900',10), 7), 2) + LEFT(LEFT('05/01/2023'+'01/01/1900',10), 2) 

select * from spg_jns_absensi where active=1