SELECT
	sdc.NIP,
	sdc.NM_PEG,
	sdc.EMAIL,
	sdc.EMAIL_2,
	sp.phone,
	sp.handphone_1,
	sp.handphone_2,
	sp.phone_kantor,
	--sp.ALAMAT_TMP_TINGGAL,
	--sdc.JNS_TMP_TINGGAL,
	--sdc.KET_TMP_TINGGAL,
	--sdc.JL_TMP_TINGGAL_CUR,
	--sdc.NO_TMP_TINGGAL_CUR,
	--sdc.RT_TMP_TINGGAL_CUR,
	--sdc.RW_TMP_TINGGAL_CUR,
	--sdc.TMT_TMP_TINGGAL,
	--sdc.JL_TMP_TINGGAL_KTP,
	--sdc.JL_TMP_TINGGAL_SURAT
	almt_tinggal.almt_tinggal,
	almt_ktp.almt_ktp,
	almt_srt.almt_srt,
	esl.NM_ESELON,
     COALESCE('' + NULLIF(es1.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es2.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es3.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es4.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es5.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es6.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es7.NM_UNIT_ORG, ''), '') + 
     COALESCE(' > ' + NULLIF(es8.NM_UNIT_ORG, ''), '') AS NM_UNIT_ES,
     suo.NM_UNIT_ORG
FROM
	SPG_DATA_CURRENT AS sdc INNER JOIN
	SPG_PEGAWAI AS sp ON sdc.NIP = sp.NIP 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 LEFT JOIN
			SPG_KECAMATAN AS kec_ting ON tmp_ting.KD_KECAMATAN = kec_ting.KD_KECAMATAN LEFT JOIN
			SPG_KABUPATEN AS kab_ting ON tmp_ting.KD_KABUPATEN = kab_ting.KD_KABUPATEN 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
	) AS almt_tinggal 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 LEFT JOIN
			SPG_KECAMATAN AS kec_ktp ON tmp_ktp.KD_KECAMATAN = kec_ktp.KD_KECAMATAN LEFT JOIN
			SPG_KABUPATEN AS kab_ktp ON tmp_ktp.KD_KABUPATEN = kab_ktp.KD_KABUPATEN 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
	) AS almt_ktp 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 LEFT JOIN
			SPG_KECAMATAN AS kec_srt ON tmp_srt.KD_KECAMATAN = kec_srt.KD_KECAMATAN LEFT JOIN
			SPG_KABUPATEN AS kab_srt ON tmp_srt.KD_KABUPATEN = kab_srt.KD_KABUPATEN 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
	) AS almt_srt  LEFT JOIN
	SPG_08_UNIT_ORGANISASI AS suo ON sdc.KD_UNIT_ORG = suo.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es1 ON suo.KD_UNIT_ES1 = es1.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es2 ON suo.KD_UNIT_ES2 = es2.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es3 ON suo.KD_UNIT_ES3 = es3.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es4 ON suo.KD_UNIT_ES4 = es4.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es5 ON suo.KD_UNIT_ES5 = es5.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es6 ON suo.KD_UNIT_ES6 = es6.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es7 ON suo.KD_UNIT_ES7 = es7.KD_UNIT_ORG LEFT JOIN
     SPG_08_UNIT_ORGANISASI AS es8 ON suo.KD_UNIT_ES8 = es8.KD_UNIT_ORG LEFT JOIN
     SPG_ESELON AS esl ON sdc.KD_ESELON = esl.KD_ESELON
WHERE
	sdc.TMT_PENSIUN >= '2020-01-01'
	AND sdc.TMT_PENSIUN < '2021-01-01'
	AND sdc.STS_PENSIUN = '1'


--SELECT * FROM SPG_RIWAYAT_TMP_TINGGAL_PEG WHERE NIP = 'P02026'