--/****** Script for SelectTopNRows command from SSMS  ******/
--SELECT TOP (1000) [PKR_ID]
--      ,[PRF_ID]
--      ,[NAMA_PERUSAHAAN]
--      ,[JABATAN_PERUSAHAAN_LAMA]
--      ,[GAJI_POKOK_BARU]
--      ,[KET_MASA_KERJA]
--      ,[TMT_MASA_KERJA]
--      ,[TMT_MASA_KERJA_BERAKHIR]
--      ,[KD_STATUS_VALIDASI]
--      ,[CRE_BY]
--      ,[CRE_TM]
--      ,[UPD_BY]
--      ,[UPD_TM]
--  FROM [OJKRecruitmentDB].[dbo].[Profile_Pengalaman_Kerja]
--ORDER BY [PRF_ID]

SELECT
	--pp.PRF_ID,
	--pp.NM_PEG,
	--LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA, pend.TAHUN_LULUS, ppk.TMT_MASA_KERJA) AS PreviousQuota, 
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pp.PRF_ID
		ELSE ''
	END AS PRF_ID,
	--pp.PRF_ID,
	--pp.NM_PEG,
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pp.NM_PEG
		ELSE ''
	END AS NM_PEG,
	--mk.NM_KELAMIN_PEG,
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mk.NM_KELAMIN_PEG
		ELSE ''
	END AS NM_KELAMIN_PEG,
	--ma.NM_AGAMA,
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN ma.NM_AGAMA
		ELSE ''
	END AS NM_AGAMA,
	--pend.NM_PEND_UMUM,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.NM_PEND_UMUM
	--	ELSE ''
	--END AS NM_PEND_UMUM,
	----pend.NM_LEMB_PEND_FORM,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.NM_LEMB_PEND_FORM
	--	ELSE ''
	--END AS NM_LEMB_PEND_FORM,
	----pend.FAKULTAS,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.FAKULTAS
	--	ELSE ''
	--END AS FAKULTAS,
	----pend.JURUSAN,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.JURUSAN
	--	ELSE ''
	--END AS JURUSAN,
	----pend.NILAI,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.NILAI
	--	ELSE ''
	--END AS NILAI,
	----pend.STATUS_PT,
	--CASE
	--	WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN pend.STATUS_PT
	--	ELSE ''
	--END AS STATUS_PT,
	--pt.TAHAP_1 AS [Tahap Administrasi],
 --    pt.TAHAP_2 AS [Tahap Psikometri, KeOJKan, B. Inggris],
 --    pt.TAHAP_3 AS [Tahap Kesehatan],
 --    pt.TAHAP_4 AS [Tahap Wawancara],
 --    pt.TAHAP_5 AS [Tahap Pemberkasan]
	--mtn1.KETERANGAN AS [Tahap Administrasi],
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mtn1.KETERANGAN
		ELSE ''
	END AS [Tahap Administrasi],
     --mtn2.KETERANGAN AS [Tahap Psikometri, KeOJKan, B. Inggris],
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mtn2.KETERANGAN
		ELSE ''
	END AS [Tahap Psikometri, KeOJKan, B. Inggris],
     --mtn3.KETERANGAN AS [Tahap Kesehatan],
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mtn3.KETERANGAN
		ELSE ''
	END AS [Tahap Kesehatan],
     --mtn4.KETERANGAN AS [Tahap Wawancara],
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mtn4.KETERANGAN
		ELSE ''
	END AS [Tahap Wawancara],
     --mtn5.KETERANGAN AS [Tahap Pemberkasan],
	CASE
		WHEN pp.PRF_ID <> LAG(pp.PRF_ID, 1,0) OVER (ORDER BY pp.PRF_ID, ppk.TMT_MASA_KERJA) THEN mtn5.KETERANGAN
		ELSE ''
	END AS [Tahap Pemberkasan],
	ppk.NAMA_PERUSAHAAN,
     ppk.JABATAN_PERUSAHAAN_LAMA,
     ppk.GAJI_POKOK_BARU,
     --ppk.KET_MASA_KERJA,
     ppk.TMT_MASA_KERJA,
     ppk.TMT_MASA_KERJA_BERAKHIR,
	 COALESCE(kerja.pengalaman_kerja, 0)
FROM
	Profile_Pelamar AS pp LEFT JOIN
	Master_Kelamin AS mk ON pp.JNS_KELAMIN_PEG = mk.JNS_KELAMIN_PEG LEFT JOIN
	Master_Agama AS ma ON pp.KD_AGAMA = ma.KD_AGAMA 
	--LEFT JOIN
	--(
	--	SELECT
	--		pnd.PRF_ID,
	--		pnd.KD_PEND_UMUM,
	--		mp.NM_PEND_UMUM,
	--		pnd.NM_LEMB_PEND_FORM,
	--		pnd.FAKULTAS,
	--		pnd.JURUSAN,
	--		pnd.NILAI,
	--		mu.STATUS,
	--		pnd.NEGARA,
	--		CASE
	--			WHEN pnd.NEGARA = 'INDONESIA' THEN mu.STATUS
	--			ELSE CASE 
	--					WHEN COALESCE(mu.STATUS, '') <> '' THEN mu.STATUS
	--					ELSE 'Luar Negeri'
	--				END
	--		END AS STATUS_PT,
	--		pnd.TAHUN_LULUS
	--	FROM
	--		(
	--			SELECT *
	--			FROM Profile_Pendidikan
	--			UNION ALL
	--			SELECT *
	--			FROM Profile_Pendidikan_Riwayat
	--		) AS pnd LEFT JOIN
	--		Master_Pendidikan AS mp ON pnd.KD_PEND_UMUM = mp.KD_PEND_UMUM LEFT JOIN
	--		Master_Universitas AS mu ON pnd.NM_LEMB_PEND_FORM = mu.UNIVERSITAS
	--	--ORDER BY
	--	--	pnd.PRF_ID,
	--	--	pnd.KD_PEND_UMUM,
	--	--	pnd.TAHUN_LULUS
	--) AS pend ON pp.PRF_ID = pend.PRF_ID 
	INNER JOIN
	Profile_Tahapan AS pt ON pp.PRF_ID = pt.PRF_ID INNER JOIN
	Master_Tahapan_Nilai AS mtn1 ON pt.TAHAP_1 = mtn1.NILAI INNER JOIN
	Master_Tahapan_Nilai AS mtn2 ON pt.TAHAP_2 = mtn2.NILAI INNER JOIN
	Master_Tahapan_Nilai AS mtn3 ON pt.TAHAP_3 = mtn3.NILAI INNER JOIN
	Master_Tahapan_Nilai AS mtn4 ON pt.TAHAP_4 = mtn4.NILAI INNER JOIN
	Master_Tahapan_Nilai AS mtn5 ON pt.TAHAP_5 = mtn5.NILAI LEFT JOIN
	(
		SELECT
			PRF_ID,
			COUNT(*) AS pengalaman_kerja
		FROM
			Profile_Pengalaman_Kerja
		GROUP BY
			PRF_ID
	) AS kerja ON pp.PRF_ID = kerja.PRF_ID LEFT JOIN
	Profile_Pengalaman_Kerja AS ppk ON pp.PRF_ID = ppk.PRF_ID
WHERE 
	pp.STS_SUBMIT = 1
	AND pp.PRF_ID <> '000'
	AND COALESCE(kerja.pengalaman_kerja, 0) > 0
	--AND COALESCE(kerja.pengalaman_kerja, 0) = 0
ORDER BY
	pp.PRF_ID,
	--ppk.TMT_MASA_KERJA,
	--pend.TAHUN_LULUS,
	ppk.TMT_MASA_KERJA