use HRIS

SELECT 
	bid1.NM_UNIT_ORG AS BIDANG,
	suo1.KD_UNIT_ORG,
	suo1.NM_UNIT_ORG,
	suo1.UNIT_SEBELUMNYA,
	--LTRIM(s.Item) AS UNIT_SEBELUMNYA,
	suo2.KD_UNIT_ORG AS KD_UNIT_ORG_SEBELUMNYA,
	suo2.NM_UNIT_ORG AS NM_UNIT_ORG_SEBELUMNYA
FROM 
	SPG_08_UNIT_ORGANISASI AS suo1
	LEFT JOIN
	SPG_08_UNIT_ORGANISASI AS bid1 ON suo1.KD_UNIT_ES1 = bid1.KD_UNIT_ORG
	OUTER APPLY
	[dbo].[SplitStrings_CTE] (suo1.UNIT_SEBELUMNYA,',') AS s
	LEFT JOIN
	SPG_08_UNIT_ORGANISASI AS suo2 ON suo2.KD_UNIT_ORG = LTRIM(s.Item)
WHERE
	suo1.TGL_AKTIF > '2022-12-31'
	AND (
		COALESCE(suo1.TGL_END, '1900-01-01') = '1900-01-01'
		OR COALESCE(suo1.TGL_END, GETDATE()) >= GETDATE()
		)
	--and left(suo1.kd_unit_org, 3) = '111'
ORDER BY
	suo1.KD_UNIT_ORG

--select * from SPG_08_UNIT_ORGANISASI where left(kd_unit_org, 3) = '111' AND (
--		COALESCE(TGL_END, '1900-01-01') = '1900-01-01'
--		OR COALESCE(TGL_END, GETDATE()) >= GETDATE()
--		) and TGL_AKTIF > '2022-12-31'
--select * from SPG_08_UNIT_ORGANISASI where kd_unit_org = '103000003000001000000000'