/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ID]
      ,[NIP]
      ,[NO_SK]
      ,[DIKELUARKAN_OLEH]
      ,[TMT_JAB_PEG]
      ,[KD_KANTOR]
      ,[JNS_JABATAN]
      ,[KD_JABATAN_STR]
      ,[KD_UNIT_ORG]
      ,[KD_PANGKAT]
      ,[GRADE]
      ,[KD_JABATAN_FUNG]
      ,[KD_PEL_FUNG]
      ,[KD_ESELON]
      ,[KD_UNIT_LUAR]
      ,[STATUS_JABATAN]
      ,[ALASAN_PJ]
      ,[KD_STATUS_VALIDASI]
      ,[KD_KANTOR_OLD]
      ,[KD_JABATAN_STR_OLD]
      ,[KD_JABATAN_FUNG_OLD]
      ,[KD_UNIT_ORG_OLD]
      ,[THN_REOG]
      ,[GRADE_TMP]
      ,[TMT_PENEMPATAN]
      ,[NM_UNIT_ORG_LAIN]
      ,[NM_JABATAN]
      ,[TANGGAL_BUAT]
      ,[NAMA_BUAT]
      ,[TANGGAL_UBAH]
      ,[NAMA_UBAH]
      ,[KD_GROUP_FUNGSIONAL]
      ,[CMP_ID]
      ,[KSO_KASUSNOMOR]
      ,[KD_GRADE]
      ,[NO_SURAT]
      ,[KD_GOL]
      ,[TGL_AKHIR]
      ,[tgl_pelaksanaan_tugas]
      ,[TANGGAL_END]
      ,[STS_RIWAYAT_JABATAN]
      ,[MASA_DINAS]
      ,[KETERANGAN]
      ,[TMT_SATKER]
      ,[TMT_REOG]
      ,[NO_URUT_JAB]
      ,[TANGGAL_RIWAYAT]
      ,[PERSETUJUAN_NILAI_KINERJA]
  FROM [HRIS].[dbo].[SPG_08_RIWAYAT_JABATAN]
select * from SPG_08_JABATAN_STR
select * from SPG_DATA_CURRENT

SELECT
	sdc.NIP,
	sdc.NM_PEG,
	almt_ktp.almt_ktp,
	almt_tinggal.almt_tinggal,
	almt_srt.almt_srt,
	srj.NM_JABATAN,
	srj.TMT_PENEMPATAN,
	srj.TMT_JAB_PEG
FROM
	SPG_08_RIWAYAT_JABATAN AS srj INNER JOIN
	SPG_DATA_CURRENT AS sdc ON srj.NIP = sdc.NIP
	 OUTER APPLY
	(
		SELECT TOP 1
			COALESCE('' + NULLIF(tmp_ktp.JLN_TMP_TINGGAL, ''), '') + 
			COALESCE(' RT. ' + NULLIF(tmp_ktp.RT_TMP_TINGGAL, ''), '') + 
			COALESCE(' RW. ' + NULLIF(tmp_ktp.RW_TMP_TINGGAL, ''), '') + 
			COALESCE(' KEL. ' + NULLIF(kel_ktp.NM_KELURAHAN, ''), '') + 
			COALESCE(' KEC. ' + NULLIF(kec_ktp.NM_KECAMATAN, ''), '') + 
			COALESCE(' KAB. ' + NULLIF(kab_ktp.NM_KABUPATEN, ''), '') + 
     		COALESCE(' PROV. ' + NULLIF(prop_ktp.NM_PROPINSI, ''), '') + 
			COALESCE(' KODE POS ' + NULLIF(tmp_ktp.KODEPOS, ''), '') AS almt_ktp
		FROM
			SPG_RIWAYAT_TMP_TINGGAL_PEG AS tmp_ktp
			LEFT JOIN
			SPG_KELURAHAN AS kel_ktp 
			ON tmp_ktp.KD_KELURAHAN = kel_ktp.KD_KELURAHAN 
			   AND tmp_ktp.KD_KECAMATAN = kel_ktp.KD_KECAMATAN 
			   AND tmp_ktp.KD_KABUPATEN = kel_ktp.KD_KABUPATEN 
			   AND tmp_ktp.KD_PROPINSI = kel_ktp.KD_PROPINSI 
			LEFT JOIN
			SPG_KECAMATAN AS kec_ktp 
			ON tmp_ktp.KD_KECAMATAN = kec_ktp.KD_KECAMATAN 
			   AND tmp_ktp.KD_KABUPATEN = kec_ktp.KD_KABUPATEN 
			   AND tmp_ktp.KD_PROPINSI = kec_ktp.KD_PROPINSI 
			LEFT JOIN
			SPG_KABUPATEN AS kab_ktp 
			ON tmp_ktp.KD_KABUPATEN = kab_ktp.KD_KABUPATEN 
			   AND tmp_ktp.KD_PROPINSI = kab_ktp.KD_PROPINSI 
			LEFT JOIN
			SPG_PROPINSI AS prop_ktp 
			ON tmp_ktp.KD_PROPINSI = prop_ktp.KD_PROPINSI
		WHERE
			tmp_ktp.tmp_tinggal_ktp = '1'
			AND tmp_ktp.KD_STATUS_VALIDASI = 1
			AND tmp_ktp.NIP = sdc.NIP
		ORDER BY
			--COALESCE(tmp_ktp.TANGGAL_UBAH, tmp_ktp.TANGGAL_BUAT, GETDATE()) DESC
			tmp_ktp.TANGGAL_UBAH DESC, 
			tmp_ktp.TANGGAL_BUAT DESC
	) AS almt_ktp
	OUTER APPLY
	(
		SELECT TOP 1
			COALESCE('' + NULLIF(tmp_ting.JLN_TMP_TINGGAL, ''), '') + 
			COALESCE(' RT. ' + NULLIF(tmp_ting.RT_TMP_TINGGAL, ''), '') + 
			COALESCE(' RW. ' + NULLIF(tmp_ting.RW_TMP_TINGGAL, ''), '') + 
			COALESCE(' KEL. ' + NULLIF(kel_ting.NM_KELURAHAN, ''), '') + 
			COALESCE(' KEC. ' + NULLIF(kec_ting.NM_KECAMATAN, ''), '') + 
			COALESCE(' KAB. ' + NULLIF(kab_ting.NM_KABUPATEN, ''), '') + 
     		COALESCE(' PROV. ' + NULLIF(prop_ting.NM_PROPINSI, ''), '') + 
			COALESCE(' KODE POS ' + NULLIF(tmp_ting.KODEPOS, ''), '') AS almt_tinggal
		FROM
			SPG_RIWAYAT_TMP_TINGGAL_PEG AS tmp_ting 
			LEFT JOIN
			SPG_KELURAHAN AS kel_ting 
			ON tmp_ting.KD_KELURAHAN = kel_ting.KD_KELURAHAN 
			   AND tmp_ting.KD_KECAMATAN = kel_ting.KD_KECAMATAN 
			   AND tmp_ting.KD_KABUPATEN = kel_ting.KD_KABUPATEN 
			   AND tmp_ting.KD_PROPINSI = kel_ting.KD_PROPINSI 
			LEFT JOIN
			SPG_KECAMATAN AS kec_ting 
			ON tmp_ting.KD_KECAMATAN = kec_ting.KD_KECAMATAN 
			   AND tmp_ting.KD_KABUPATEN = kec_ting.KD_KABUPATEN 
			   AND tmp_ting.KD_PROPINSI = kec_ting.KD_PROPINSI 
			LEFT JOIN
			SPG_KABUPATEN AS kab_ting 
			ON tmp_ting.KD_KABUPATEN = kab_ting.KD_KABUPATEN 
			   AND tmp_ting.KD_PROPINSI = kab_ting.KD_PROPINSI 
			LEFT JOIN
			SPG_PROPINSI AS prop_ting 
			ON tmp_ting.KD_PROPINSI = prop_ting.KD_PROPINSI
		WHERE
			tmp_ting.HOME_BASE = '1'
			AND tmp_ting.KD_STATUS_VALIDASI = 1
			AND tmp_ting.NIP = sdc.NIP
		ORDER BY
			--COALESCE(tmp_ting.TANGGAL_UBAH, tmp_ting.TANGGAL_BUAT, GETDATE()) DESC
			tmp_ting.TANGGAL_UBAH DESC, 
			tmp_ting.TANGGAL_BUAT DESC
	) AS almt_tinggal OUTER APPLY
	(
		SELECT TOP 1
			COALESCE('' + NULLIF(tmp_srt.JLN_TMP_TINGGAL, ''), '') + 
			COALESCE(' RT. ' + NULLIF(tmp_srt.RT_TMP_TINGGAL, ''), '') + 
			COALESCE(' RW. ' + NULLIF(tmp_srt.RW_TMP_TINGGAL, ''), '') + 
			COALESCE(' KEL. ' + NULLIF(kel_srt.NM_KELURAHAN, ''), '') + 
			COALESCE(' KEC. ' + NULLIF(kec_srt.NM_KECAMATAN, ''), '') + 
			COALESCE(' KAB. ' + NULLIF(kab_srt.NM_KABUPATEN, ''), '') + 
     		COALESCE(' PROV. ' + NULLIF(prop_srt.NM_PROPINSI, ''), '') + 
			COALESCE(' KODE POS ' + NULLIF(tmp_srt.KODEPOS, ''), '') AS almt_srt
		FROM
			SPG_RIWAYAT_TMP_TINGGAL_PEG AS tmp_srt 
			LEFT JOIN
			SPG_KELURAHAN AS kel_srt 
			ON tmp_srt.KD_KELURAHAN = kel_srt.KD_KELURAHAN 
			   AND tmp_srt.KD_KECAMATAN = kel_srt.KD_KECAMATAN 
			   AND tmp_srt.KD_KABUPATEN = kel_srt.KD_KABUPATEN 
			   AND tmp_srt.KD_PROPINSI = kel_srt.KD_PROPINSI 
			LEFT JOIN
			SPG_KECAMATAN AS kec_srt 
			ON tmp_srt.KD_KECAMATAN = kec_srt.KD_KECAMATAN 
			   AND tmp_srt.KD_KABUPATEN = kec_srt.KD_KABUPATEN 
			   AND tmp_srt.KD_PROPINSI = kec_srt.KD_PROPINSI 
			LEFT JOIN
			SPG_KABUPATEN AS kab_srt 
			ON tmp_srt.KD_KABUPATEN = kab_srt.KD_KABUPATEN 
			   AND tmp_srt.KD_PROPINSI = kab_srt.KD_PROPINSI 
			LEFT JOIN
			SPG_PROPINSI AS prop_srt 
			ON tmp_srt.KD_PROPINSI = prop_srt.KD_PROPINSI
		WHERE
			tmp_srt.tmp_tinggal_ktp = '1'
			AND tmp_srt.KD_STATUS_VALIDASI = 1
			AND tmp_srt.NIP = sdc.NIP
		ORDER BY
			--COALESCE(tmp_srt.TANGGAL_UBAH, tmp_srt.TANGGAL_BUAT, GETDATE()) DESC
			tmp_srt.TANGGAL_UBAH DESC, 
			tmp_srt.TANGGAL_BUAT DESC
	) AS almt_srt
WHERE
	srj.KD_JABATAN_STR IN ('001', '002', '004')
	AND sdc.STS_PENSIUN = '1'
ORDER BY
	srj.TMT_JAB_PEG DESC
