Hi @jennifer zen ,
It could be better for you to post some sample data and expected output so that we could proceed with testing.
My question is I want to delete all the records in the dbo.DIM_AGENT table which are not in the [dbo].[FACT_COMMISSION] table first and then delete all the records for which ever year I want...
If you would like to delete all the records in dbo.DIM_AGENT table which are not in the [dbo].[FACT_COMMISSION] table, below query could be work:
DELETE A
FROM [dbo].[DIM_AGENTS] A
LEFT JOIN [dbo].[FACT_COMMISSION] F ON A.AGENT_KEY = F.AGENT_KEY
WHERE F.AGENT_KEY IS NULL
As mentioned by Erland, you could use NOT EXISTS . But a small modification on his query as below:
DELETE A
FROM DIM_AGENTS A
WHERE NOT EXISTS (SELECT *
FROM FACT_COMMISSION F
JOIN Dim_Calendar C ON F.DateKey = C.DateKey
WHERE A.AGENT_KEY = F.AGENT_KEY
AND C.CalendarYearMonth >= 201601)
Or using NOT IN:
DELETE A
FROM DIM_AGENTS A
WHERE AGENT_KEY NOT IN (SELECT AGENT_KEY
FROM FACT_COMMISSION F
JOIN Dim_Calendar C ON F.DateKey = C.DateKey
WHERE A.AGENT_KEY = F.AGENT_KEY
AND C.CalendarYearMonth >= 201601)
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.