select * from dbo.SPG_PEGAWAI_ELIGIBLE where PERIODE_PEMENUHAN = 'MPP-000003' AND STS_PENSIUN = 0 and kd_gol = '40' and LEFT(kd_bidang,3) = '103'

--AND d.KD_GOL = a.KD_GOL
--AND d.KD_JABATAN_STR = a.KD_JABATAN

--exec rep_pegawai_eligible_yang_melamar_lowongan_jabatan 'MPP-000003'

select bidang, count(*) from dbo.SPG_PEGAWAI_ELIGIBLE where PERIODE_PEMENUHAN = 'MPP-000003' AND STS_PENSIUN = 0 group by bidang
select LEFT(kd_bidang,3), count(*) from dbo.SPG_PEGAWAI_ELIGIBLE where PERIODE_PEMENUHAN = 'MPP-000003' AND STS_PENSIUN = 0 group by LEFT(kd_bidang,3)

declare @tgl date = '2020-11-04'
select * 
from 
	rec_pelamar_internal as a --inner join
	--rec_pelamar_aktifitas as b on a.ID = b.ID_PELAMAR
	cross apply
	(
		select ID_PELAMAR from rec_pelamar_aktifitas as b
		where 
		a.ID = b.ID_PELAMAR
		AND b.STS_PELAMAR = 0
		AND CAST(b.TGL_BUAT_AKTIFITAS AS DATE) <= @tgl
		AND CAST(b.TGL_BUAT_AKTIFITAS AS DATE) >= @tgl
		group by ID_PELAMAR
	) as c
where
	COALESCE(a.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl
	

--select * from rec_pelamar_internal where ID=127
select * from rec_pelamar_aktifitas where ID_PELAMAR=127 order by tgl_buat_aktifitas

select * from rec_pelamar_aktifitas where ID_PELAMAR in (62,61,27) order by tgl_buat_aktifitas

select * from rec_pelamar_aktifitas where ID_PELAMAR=127 and 

select * from rec_pelamar_internal where id not in (select ID_PELAMAR from rec_pelamar_aktifitas 
where sts_pelamar=0) and id in (select ID_PELAMAR from rec_pelamar_aktifitas where sts_pelamar<>0) order by tanggal_apply

0 2020-11-01
1 2020-11-01
2 2020-11-01

--update rec_pelamar_aktifitas set tgl_buat_aktifitas='2020-11-05 14:13:55.940' where ID_PELAMAR=127 and id=58

--INSERT INTO [dbo].[REC_PELAMAR_AKTIFITAS]
--           ([ID_PELAMAR]
--           ,[ID_FORMASI]
--           ,[STS_PELAMAR]
--           ,[KET_STS_PELAMAR]
--           ,[TGL_BUAT_AKTIFITAS]
--           ,[NIP_BUAT_AKTIFITAS]
--           ,[MPP_ID])
--     VALUES
--           (151
--           ,18143
--           ,0
--           ,'Menunggu Rekomendasi Pimpinan Satker'
--           ,'2020-11-04 14:13:55'
--           ,'H02774'
--           ,'MPP-000003')
--GO



declare @tgl date = '2020-11-04'
--select * 
--from 
--	rec_pelamar_internal as a --inner join
--	--rec_pelamar_aktifitas as b on a.ID = b.ID_PELAMAR
--	cross apply
--	(
--		select ID_PELAMAR from rec_pelamar_aktifitas as b
--		where 
--		a.ID = b.ID_PELAMAR
--		AND b.STS_PELAMAR = 0
--		AND CAST(b.TGL_BUAT_AKTIFITAS AS DATE) <= @tgl
--		AND CAST(b.TGL_BUAT_AKTIFITAS AS DATE) >= @tgl
--		group by ID_PELAMAR
--	) as c
--where
--	COALESCE(a.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl

			select count(*), 
			d.id_formasi
			from 
				rec_pelamar_internal as d
				cross apply
				(
					select ID_PELAMAR from rec_pelamar_aktifitas as e
					where 
					d.ID = e.ID_PELAMAR
					AND e.STS_PELAMAR = 0
					AND CAST(e.TGL_BUAT_AKTIFITAS AS DATE) <= @tgl
					AND CAST(e.TGL_BUAT_AKTIFITAS AS DATE) >= @tgl
					group by ID_PELAMAR
				) as c
			where
				COALESCE(d.TGL_REKOMENDASI_PIMPINAN, GETDATE()) >= @tgl
			group by
				d.id_formasi

select * from REC_RENCANA_PEMENUHAN_PEG where id_formasi in (18143, 18176, 8618)

--select * from SPG_PEGAWAI_ELIGIBLE where nip = '01143'
select * from rec_pelamar_internal where nip = '01143' and tanggal_apply = '2020-11-26' --and rpi_id = 'RPI-000140'
select * from rec_pelamar_aktifitas where ID_PELAMAR in (1215, 1216)

--update rec_pelamar_internal set tanggal_apply = '2020-11-04' where nip = '01143' and tanggal_apply = '2020-11-26' 
select * from rec_pelamar_aktifitas where ID_PELAMAR = 151

--update rec_pelamar_aktifitas set tgl_buat_aktifitas = '2020-11-04 22:42:05.517' where ID_PELAMAR = 1213
--update rec_pelamar_aktifitas set tgl_buat_aktifitas = '2020-11-04 22:42:31.220' where ID_PELAMAR = 1216

select * from REC_PERMINTAAN_ADVERTISING
--update REC_PERMINTAAN_ADVERTISING set jam_selesai = '23:59' where tanggal_selesai = '2020-11-26'

--exec dbo.rep_pelamar_sebelum_rekomendasi 'MPP-000003', '2020-11-04'

select * from SPG_PEGAWAI_ELIGIBLE

			SELECT
				COUNT(*) AS eligible
			FROM
				dbo.SPG_PEGAWAI_ELIGIBLE AS d
			WHERE
				d.PERIODE_PEMENUHAN = lowjab.MPP_ID
				AND d.STS_PENSIUN = 0
				AND d.KD_GOL = lowjab.KD_GOL
				AND d.KD_JABATAN_STR = lowjab.KD_JABATAN	-- ?

