比较SQL中以逗号分隔的字符串

我有以下临时表:

CREATE TABLE #COMPANY_CHANGE 
        (       
        Risk_ID VARCHAR(50),
        LINKED_COMPANIES VARCHAR(MAX),
        PERIOD VARCHAR(20)

-- Populate the #COMPANY_CHANGE 
)

INSERT INTO #COMPANY_CHANGE (Risk_ID, LINKED_COMPANIES,PERIOD)
VALUES(1, 'X,y,z', 202202),
        (1, 'X,y', 202208),
        (2, 'A,B,C', 202202),
        (2, 'B,C,D', 202208),
      (4,'Z',202202),
      (4,'Z',202208) 

因此，对于每个Risk_ID，我try 比较起始时间段=202202和结束时间段=202208中的Linked_Companies，例如，对于Risk_ID=1，我们有3个公司链接在202202上，即X，y，z，但在结束时间段202208中，我们只有两个公司X，y，这意味着公司z已被移除

我想要的输出应该是这样的

Risk_ID  PERIOD   Change  Company_Added Company_Removed
1         202208   Yes       Null               z
2         202208   Yes       D                  A
4         202208   No        NUll               Null

下面是我的代码:

WITH CompanyChanges AS (
  SELECT
    RISK_ID,
    PERIOD,
    LINKED_COMPANIES,
    LAG(LINKED_COMPANIES) OVER (PARTITION BY RISK_ID ORDER BY PERIOD) AS PREVIOUS_LINKED_COMPANIES
  FROM COMPANY_CHANGE
)
SELECT
  RISK_ID,
  PERIOD,
  STRING_AGG(REPLACE(LINKED_COMPANIES, PREVIOUS_LINKED_COMPANIES, ''), ',') AS Change
FROM CompanyChanges
WHERE PREVIOUS_LINKED_COMPANIES IS NOT NULL AND PREVIOUS_LINKED_COMPANIES <> LINKED_COMPANIES
GROUP BY RISK_ID, PERIOD
ORDER BY RISK_ID, PERIOD;