SELECT
	sdc.NIP,
	sdc.NM_PEG,
	sdc.NM_JNS_KELAMIN_PEG,
	--sdc.STS_PERKAWINAN,
	g.ket_sts_kawin,
	sdc.TMT_OJK,
	dbo.MasaDinasTahunBulanDDay(sdc.TGL_LAHIR_PEG,sdc.TMT_OJK) AS [USIA PER TMT OJK],
	sdc.NIP_LAMA,
	lama.TMT_OJK,
	pdd.pendidikan_terakhir,
	m.NM_GOLONGAN LEVELJABATAN,
	c.nm_jabatan_str jabatan,
	CASE a.sts_penerimaan_peg
		WHEN '100' THEN 'PCT (Pendidikan Calon PTU)'
          WHEN '200' THEN 'PCS (Pendidikan Calon Staf)'
          WHEN '300' THEN 'PCP (Pendidikan Calon Pejabat)'
          WHEN '400' THEN 'PCE (Pendidikan Calon Staf Edukasi dan Perlindungan Konsumen)'
          WHEN '500' THEN 'Ex Penugasan'
          WHEN '600' THEN 'Ex PKWT'
	END AS Jalur_Penerimaan,
	a.angkatan,
	a.tahun_perekrutan,
	CASE sdc.STS_PENSIUN WHEN 0 THEN 'Aktif' ELSE 'Tidak Aktif' END statuspegawai,
	j.NM_STATUS_KEPEG tipepegawai,
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES1) [bidang],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES2) [depkom],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES3) [satuan_kerja],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES4) [unit_kerja],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES5) [bagian],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES6) [bagian],
	(select top(1) NM_UNIT_ORG from SPG_08_UNIT_ORGANISASI where KD_UNIT_ORG = b.KD_UNIT_ES7) [subbagian],
	b.nama_satker_organisasi,
	b.SINGKATAN_UNIT_ORG
FROM
	SPG_DATA_CURRENT AS sdc
	OUTER APPLY
	(
		SELECT sdcl.TMT_OJK FROM SPG_DATA_CURRENT AS sdcl WHERE sdcl.NIP = sdc.NIP_LAMA
	) AS lama
	OUTER APPLY
	(
		SELECT TOP 1 b.nm_pend_umum + ' ' + REPLACE(a.jurusan, '&AMP;', '&') + ', ' + a.nm_lemb_pend_form AS pendidikan_terakhir
		FROM spg_riwayat_pend_formal a
			LEFT JOIN spg_jns_pend_umum b ON a.kd_pend_umum =b.kd_pend_umum
		WHERE a.nip = sdc.NIP and dbo.nvl(a.kd_status_validasi,0)='1' order by b.id desc
	) AS pdd
	left join spg_pegawai as a on sdc.nip = a.nip
	left join SPG_08_UNIT_ORGANISASI b on sdc.KD_UNIT_ORG = b.KD_UNIT_ORG
	left join spg_08_jabatan_str c on sdc.kd_jabatan_str =c.kd_jabatan_str
	left join SPG_PANGKAT l on sdc.KD_PANGKAT = l.KD_PANGKAT
    	left join SPG_GOLONGAN m on l.KD_GOL = m.KD_GOL
	left join SPG_STATUS_KEPEG j on sdc.KD_STATUS_KEPEG=j.KD_STATUS_KEPEG
	left join SPG_STS_KAWIN g on sdc.STS_PERKAWINAN =g.KD_STS_KAWIN
WHERE
	sdc.TMT_OJK >= '2020-01-01'
ORDER BY
	sdc.TMT_OJK,
	sdc.NIP


--NIP LAMA	TMT OJK NIP LAMA / CALON	PENDIDIKAN TERAKHIR	LEVEL JABATAN	JABATAN	JALUR PENERIMAAN	ANGKATAN	TAHUN	STATUS PEGAWAI	BIDANG	DEPUTI KOMISIONER	DEPARTEMEN	DIREKTORAT	DEPUTI DIREKTORAT	BAGIAN	SUB BAGIAN	SATUAN KERJA	SINGKATAN
