nip, tmt, noSK, tglSK, tmt efektif, satker, dan unit

reverse(stuff(reverse('a,b,c,d,'), 1, 1, ''))
SELECT REVERSE(SUBSTRING(REVERSE(@a), 2, 9999))

select --CONCAT_WS(' > ', b.nm_unit_org, c.nm_unit_org, d.nm_unit_org, e.nm_unit_org, f.nm_unit_org, g.nm_unit_org, h.nm_unit_org), b.nm_unit_org as bidang, b.kd_eselon as es1, c.nm_unit_org as deputi_komisioner, c.kd_eselon as es2, d.nm_unit_org as departemen, d.kd_eselon as es3, 
REVERSE(SUBSTRING(REVERSE(CONCAT('',
    CASE WHEN b.nm_unit_org IS NULL THEN '' ELSE CONCAT(b.nm_unit_org,' > ') END,
    CASE WHEN c.nm_unit_org IS NULL THEN '' ELSE CONCAT(c.nm_unit_org,' > ') END,
    CASE WHEN d.nm_unit_org IS NULL THEN '' ELSE CONCAT(d.nm_unit_org,' > ') END,
    CASE WHEN e.nm_unit_org IS NULL THEN '' ELSE CONCAT(e.nm_unit_org,' > ') END,
    CASE WHEN f.nm_unit_org IS NULL THEN '' ELSE CONCAT(f.nm_unit_org,' > ') END,
    CASE WHEN g.nm_unit_org IS NULL THEN '' ELSE CONCAT(g.nm_unit_org,' > ') END,
    CASE WHEN h.nm_unit_org IS NULL THEN '' ELSE CONCAT(h.nm_unit_org,' > ') END)), 4, 9999)),
			e.nm_unit_org as direktorat, e.kd_eselon as es4, 
			f.nm_unit_org as deputi_direktur, f.kd_eselon as es5, g.nm_unit_org as bagian, g.kd_eselon as es6, h.nm_unit_org as subbagian, h.kd_eselon as es7
			from SPG_08_UNIT_ORGANISASI a
			left join SPG_08_UNIT_ORGANISASI b on a.kd_unit_es1=b.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI c on a.kd_unit_es2=c.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI d on a.kd_unit_es3=d.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI e on a.kd_unit_es4=e.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI f on a.kd_unit_es5=f.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI g on a.kd_unit_es6=g.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI h on a.kd_unit_es7=h.kd_unit_org
			where a.kd_unit_org='101016001000000000000000'

SELECT 
	srj.NIP,
	sdc.NM_PEG,
	srj.kd_unit_org,
	satker.satkerunit,
	ktr.nm_kantor,
	srj.kd_jabatan_str,
	jbt.nm_jabatan_str,
	srj.tmt_jab_peg,
	srj.tmt_satker,
	srj.tanggal_riwayat,
	srj.no_sk,
	sk.tgl_sk,
	sjb.nm_status_jabatan,
	srj.sts_riwayat_jabatan,
	case srj.sts_riwayat_jabatan
		when '1' then 'Promosi'
		when '2' then 'Promosi Mutasi'
		when '3' then 'Mutasi'
		when '4' then 'Rotasi'
		when '5' then 'Penyesuaian Jabatan'
		when '6' then 'Penetapan'
		when '7' then 'Penetapan Kembali'
		when '8' then 'Reorganisasi'
		when '9' then 'Demosi'
		when '10' then 'Orientasi'
	end AS [Status Riwayat Jabatan],
	*
FROM 
	spg_08_riwayat_jabatan AS srj inner join
	spg_data_current AS sdc ON srj.NIP = sdc.NIP LEFT JOIN
	spg_08_kantor AS ktr ON srj.kd_kantor = ktr.kd_kantor LEFT JOIN
	spg_08_jabatan_str AS jbt ON srj.kd_jabatan_str = jbt.kd_jabatan_str LEFT JOIN
	spg_status_jabatan AS sjb ON srj.status_jabatan = sjb.status_jabatan LEFT JOIN
	spg_sk AS sk ON srj.no_sk = sk.no_sk and sk.jns_sk='01' and sk.nip=srj.nip LEFT JOIN
	--OUTER APPLY
	(
		select REVERSE(SUBSTRING(REVERSE(CONCAT('',
			CASE WHEN b.nm_unit_org IS NULL THEN '' ELSE CONCAT(b.nm_unit_org,' > ') END,
			CASE WHEN c.nm_unit_org IS NULL THEN '' ELSE CONCAT(c.nm_unit_org,' > ') END,
			CASE WHEN d.nm_unit_org IS NULL THEN '' ELSE CONCAT(d.nm_unit_org,' > ') END,
			CASE WHEN e.nm_unit_org IS NULL THEN '' ELSE CONCAT(e.nm_unit_org,' > ') END,
			CASE WHEN f.nm_unit_org IS NULL THEN '' ELSE CONCAT(f.nm_unit_org,' > ') END,
			CASE WHEN g.nm_unit_org IS NULL THEN '' ELSE CONCAT(g.nm_unit_org,' > ') END,
			CASE WHEN h.nm_unit_org IS NULL THEN '' ELSE CONCAT(h.nm_unit_org,' > ') END)), 4, 9999)) AS satkerunit,
			a.kd_unit_org
		from SPG_08_UNIT_ORGANISASI a
			left join SPG_08_UNIT_ORGANISASI b on a.kd_unit_es1=b.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI c on a.kd_unit_es2=c.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI d on a.kd_unit_es3=d.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI e on a.kd_unit_es4=e.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI f on a.kd_unit_es5=f.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI g on a.kd_unit_es6=g.kd_unit_org
			left join SPG_08_UNIT_ORGANISASI h on a.kd_unit_es7=h.kd_unit_org
		--where a.kd_unit_org=srj.kd_unit_org
	) AS satker ON satker.kd_unit_org=srj.kd_unit_org
WHERE
	srj.tanggal_riwayat >= '2016-01-01'
	--srj.tanggal_riwayat < '2016-01-01'
	AND srj.KD_STATUS_VALIDASI = 1
	--AND
	--	(
	--		jbt.nm_jabatan_str LIKE '%advisor%'
	--		OR jbt.nm_jabatan_str LIKE '%analis eksekutif%'
	--	)
ORDER BY
	srj.NIP,
	srj.tanggal_riwayat



select * from sts_riwayat_jabatan