--- izin (dikosongin aja ga ada istilah itu di ojk)
--- sakit : ambil yg flagnya S sama SD
--- cuti : semua yg jenisnya cuti di ojk CT, CB, CPMP, CX, dsb


--DECLARE @StartDate DATETIME
--DECLARE @EndDate DATETIME
--SET @StartDate = '2008/10/01'
--SET @EndDate = '2008/10/31'

DECLARE @tanggal DATE = '2021-04-01'

SELECT
   (DATEDIFF(dd, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0), EOMONTH(@tanggal)) + 1)
  -(DATEDIFF(wk, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0), EOMONTH(@tanggal)) * 2)
  -(CASE WHEN DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0)) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, EOMONTH(@tanggal)) = 'Saturday' THEN 1 ELSE 0 END)
  -(SELECT COUNT(*) FROM dbo.SPG_HARI_LIBUR WHERE TANGGAL_HARI_LIBUR BETWEEN DATEADD(month, DATEDIFF(month, 0, @tanggal), 0) AND EOMONTH(@tanggal) AND NASIONAL = '1')
FROM
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG INNER JOIN
WHERE
	suo.kode_satker_organisasi = '102001001000000000000000'

--DECLARE @Table TABLE (Col1 INT, Col2 DATETIME)
--DECLARE @StartDT DATETIME

--SET @StartDT =  '20090101'

--WHILE @StartDT < '20111201'
--BEGIN
--	--PRINT CONVERT(VARCHAR,@StartDT) + '---' + convert(varchar,DATEADD(MONTH,1,@StartDT))
--	SELECT COUNT(*) FROM @Table WHERE Col2 >= @StartDT AND Col2 < DATEADD(MONTH,1,@StartDT)
--	SET @StartDT = DATEADD(MONTH,1,@StartDT)
--END


select * from spg_data_current where nm_peg like '%aidila%'
select * from SPG_08_UNIT_ORGANISASI where nama_satker_organisasi like '%aplikasi%'


select * from spg_data_current as sdc inner join SPG_08_UNIT_ORGANISASI as suo on sdc.kd_unit_org = suo.kd_unit_org where sdc.sts_pensiun = '0' and suo.kode_satker_organisasi

SELECT 
	srj.NIP,
	srj.NO_URUT_JAB,
	srj.TANGGAL_RIWAYAT,
	COALESCE(LEAD(srj.TANGGAL_RIWAYAT,1) OVER (
			ORDER BY srj.NO_URUT_JAB
		), CAST(GETDATE() AS DATE)) NEXT_TANGGAL_RIWAYAT,
	suo.kode_satker_organisasi,
	suo.nama_satker_organisasi
	--srj.*
FROM 
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_08_RIWAYAT_JABATAN AS srj ON sdc.NIP = srj.NIP INNER JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON srj.KD_UNIT_ORG = suo.KD_UNIT_ORG
WHERE
	suo.kode_satker_organisasi IN ('101005000000000000000000', '101005002000000000000000', '102001001000000000000000', '101013001000000000000000')
	--AND sdc.NIP = '00345'
	AND sdc.NIP IN ('00345', '03313')
	--AND srj.TANGGAL_RIWAYAT >= @tanggal
	--AND 
	--srj.TMT_PENEMPATAN,
	--srj.TMT_JAB_PEG
select nip, NO_URUT_JAB, TANGGAL_RIWAYAT from SPG_08_RIWAYAT_JABATAN where nip IN ('00345', '03313') order by nip, NO_URUT_JAB

select TMT_JAB_PEG, TMT_PENEMPATAN, TANGGAL_RIWAYAT, NO_URUT_JAB, srj.KD_UNIT_ORG, suo.* from SPG_08_RIWAYAT_JABATAN srj left join SPG_08_UNIT_ORGANISASI AS suo ON srj.KD_UNIT_ORG = suo.KD_UNIT_ORG where nip = '00345' order by NO_URUT_JAB
select distinct kode_satker_organisasi from SPG_08_UNIT_ORGANISASI where nama_satker_organisasi LIKE '%Organisasi dan Sumber Daya Manusia%'

('101005000000000000000000',
'101005002000000000000000',
'101013001000000000000000',
'102001001000000000000000')