--KD_GOL	NM_GOLONGAN
--90	Deputi Komisioner
--80	Kepala Departemen
--70	Direktur
--60	Deputi Direktur
--50	Kepala Bagian
--40	Kepala Subbagian

DECLARE @hasil TABLE ( 
						bidang VARCHAR(100) NULL,
						--kd_gol VARCHAR(2) NULL,
						--nm_gol VARCHAR(50) NULL,
						eligible_60 SMALLINT NULL,
						jml_pelamar_60 SMALLINT NULL,
						selisih_60 SMALLINT NULL,
						eligible_50 SMALLINT NULL,
						jml_pelamar_50 SMALLINT NULL,
						selisih_50 SMALLINT NULL,
						eligible_40 SMALLINT NULL,
						jml_pelamar_40 SMALLINT NULL,
						selisih_40 SMALLINT NULL
					)
INSERT INTO @hasil
	(
		bidang,
		eligible_60,
		jml_pelamar_60,
		selisih_60,
		eligible_50,
		jml_pelamar_50,
		selisih_50,
		eligible_40,
		jml_pelamar_40,
		selisih_40
	)
SELECT
	NM_UNIT_ORG,
	0,0,0,0,0,0,0,0,0
FROM 
	SPG_08_UNIT_ORGANISASI 
WHERE 
	KD_ESELON = '00' 
	AND LEFT(KD_UNIT_ORG,3) >= '101' 
	AND LEFT(KD_UNIT_ORG,3) <= '107' 
ORDER BY 
	NO_URUT

DECLARE
	@bidang VARCHAR(100),
	@kd_gol VARCHAR(2),
	@nm_gol VARCHAR(50),
	@eligible SMALLINT,
	@jml_pelamar SMALLINT,
	@selisih SMALLINT

DECLARE list_cursor CURSOR FOR  
SELECT
	a.BIDANG,
	a.KD_GOL,
	a.NM_GOL,
	SUM(e.eligible) AS eligible,
	SUM(g.jml_pelamar) AS jml_pelamar,
	SUM(e.eligible) - SUM(g.jml_pelamar) AS selisih
FROM
	REC_RENCANA_PEMENUHAN_PEG a
	INNER JOIN
	REC_PTK b ON
		a.ID_FORMASI = b.ID_FORMASI
	INNER JOIN
	REC_PERMINTAAN_ADVERTISING c ON
		b.PTK_ID = c.REQ_ID
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS eligible
		FROM
			SPG_PEGAWAI_ELIGIBLE d
		WHERE
			a.MPP_ID = d.PERIODE_PEMENUHAN
			AND d.STS_PENSIUN = 0
			AND a.NM_GOL = d.LEVEL_JABATAN
			AND a.NM_JABATAN = d.NM_JABATAN	-- ?
	) e 
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS jml_pelamar
		FROM
			REC_PELAMAR_INTERNAL f
		WHERE
			b.PTK_ID = f.REQ_ID
			AND c.REQ_ID = f.REQ_ID
			--AND f.REKOMENDASI_PIMPINAN = '1'
	) g
WHERE
	a.STATUS_KIRIM = 1
	AND a.STATUS_AKTIF = 1
GROUP BY
	LEFT(a.KD_UNIT_ORG,3),
	a.BIDANG,
	a.KD_GOL,
	a.NM_GOL
ORDER BY
	LEFT(a.KD_UNIT_ORG,3),
	a.KD_GOL DESC;

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @nm_gol, @eligible, @jml_pelamar, @selisih  

WHILE @@FETCH_STATUS = 0  
BEGIN  

	IF EXISTS(SELECT 1 FROM @hasil WHERE bidang = @bidang)
		BEGIN
			IF @kd_gol = 40
				BEGIN
					UPDATE @hasil SET eligible_40 = @eligible, jml_pelamar_40 = @jml_pelamar, selisih_40 = @selisih WHERE bidang = @bidang
				END
			ELSE IF @kd_gol = 50
				BEGIN
					UPDATE @hasil SET eligible_50 = @eligible, jml_pelamar_50 = @jml_pelamar, selisih_50 = @selisih WHERE bidang = @bidang
				END
			ELSE IF @kd_gol = 60
				BEGIN
					UPDATE @hasil SET eligible_60 = @eligible, jml_pelamar_60 = @jml_pelamar, selisih_60 = @selisih WHERE bidang = @bidang
				END
		END
	--ELSE
	--	BEGIN
	--		IF @kd_gol = 40
	--			BEGIN
	--				INSERT INTO @hasil
	--					(
	--						bidang,
	--						eligible_40,
	--						jml_pelamar_40,
	--						selisih_40
	--					)
	--				VALUES
	--					(
	--						@bidang, -- bidang - varchar(100)nt
	--						@eligible,	-- eligible_40 - smallint
	--						@jml_pelamar,	-- jml_pelamar_40 - smallint
	--						@selisih	-- selisih_40 - smallint
	--					)
	--			END
	--		ELSE IF @kd_gol = 50
	--			BEGIN
	--				INSERT INTO @hasil
	--					(
	--						bidang,
	--						eligible_50,
	--						jml_pelamar_50,
	--						selisih_50
	--					)
	--				VALUES
	--					(
	--						@bidang, -- bidang - varchar(100)
	--						@eligible,	-- eligible_50 - smallint
	--						@jml_pelamar,	-- jml_pelamar_50 - smallint
	--						@selisih 	-- selisih_50 - smallint
	--					)
	--			END
	--		ELSE IF @kd_gol = 60
	--			BEGIN
	--				INSERT INTO @hasil
	--					(
	--						bidang,
	--						eligible_60,
	--						jml_pelamar_60,
	--						selisih_60
	--					)
	--				VALUES
	--					(
	--						@bidang, -- bidang - varchar(100)
	--						@eligible,	-- eligible_60 - smallint
	--						@jml_pelamar,	-- jml_pelamar_60 - smallint
	--						@selisih 	-- selisih_60 - smallint
	--					)
	--			END
	--END
	 

  FETCH NEXT FROM list_cursor INTO @bidang, @kd_gol, @nm_gol, @eligible, @jml_pelamar, @selisih  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

SELECT * FROM @hasil 

----------------------------------------------------

SELECT
	a.BIDANG,
	a.KD_GOL,
	a.NM_GOL,
	--b.KD_GOL,
	--b.NM_GOLONGAN,

	--b.PTK_ID,
	--a.MPP_ID,
	--b.JUMLAH AS realisasi_pemenuhan,
	--a.KD_UNIT_ORG,
	--a.NM_UNIT_ORG,
	--a.KD_KANTOR,
	--a.NM_KANTOR,
	--a.STATUS_KIRIM,
	--a.STATUS_AKTIF,
	--a.KD_SATKER,
	--a.SATKER,
	--a.DIREKTORAT,
	--a.FORMASI,
	--a.KD_JABATAN,
	--a.NM_JABATAN,
	----a.KD_GOL,
	----a.NM_GOL,
	--a.JNS_JABATAN,
	SUM(e.eligible) AS eligible,
	SUM(g.jml_pelamar) AS jml_pelamar,
	SUM(e.eligible) - SUM(g.jml_pelamar) AS selisih
FROM
	REC_RENCANA_PEMENUHAN_PEG a
	INNER JOIN
	REC_PTK b ON
		a.ID_FORMASI = b.ID_FORMASI
	INNER JOIN
	REC_PERMINTAAN_ADVERTISING c ON
		b.PTK_ID = c.REQ_ID
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS eligible
		FROM
			SPG_PEGAWAI_ELIGIBLE d
		WHERE
			a.MPP_ID = d.PERIODE_PEMENUHAN
			AND d.STS_PENSIUN = 0
			AND a.NM_GOL = d.LEVEL_JABATAN
			AND a.NM_JABATAN = d.NM_JABATAN	-- ?
	) e 
	OUTER APPLY
	(
		SELECT
			COUNT(*) AS jml_pelamar
		FROM
			REC_PELAMAR_INTERNAL f
		WHERE
			b.PTK_ID = f.REQ_ID
			AND c.REQ_ID = f.REQ_ID
			--AND f.REKOMENDASI_PIMPINAN = '1'
	) g
WHERE
	a.STATUS_KIRIM = 1
	AND a.STATUS_AKTIF = 1
GROUP BY
	LEFT(a.KD_UNIT_ORG,3),
	a.BIDANG,
	a.KD_GOL,
	a.NM_GOL
ORDER BY
	LEFT(a.KD_UNIT_ORG,3),
	a.KD_GOL DESC;