T SQL，獲取從歷史表中添加或刪除的新客戶

我有一個包含歷史數據的表。每天都會添加相同的員工名單和新的日期（每天有 1200-12500 名員工，但是今天可能只有少數員工是新添加的，或者他們可能昨天離開了公司而沒有顯示在今天的名單上）。

我想知道每天哪些員工不在昨天的名單上，這是新員工，員工昨天出現但今天沒有，因為他們離開了公司。我已經嘗試了以下但不高興請幫助我的歷史表如下所示，我需要一個腳本來僅獲取 ID4 和 ID5 的行。

SELECT StaffID, StaffNAME, CensusDate
FROM table1 AS A 
LEFT JOIN table1 AS B 

ON A.StaffID = B.StaffID AND A.date = GETDATE())  AS date) 
and B.date = CAST(DATEADD(day, -1, convert(date, GETDATE())) AS date) 
AND A.staffID IS NULL

可能有一種更簡單的方法，但我認為這可以實現您想要的：

WITH CTE_TodaysStaff AS
(
   SELECT DISTINCT T1.StaffID
   FROM table1 AS T1
   WHERE T1.CensusDate = CAST(GETDATE() AS DATE)
),
CTE_NotShowingToday AS
(
   SELECT DISTINCT T1.StaffID
   FROM table1 AS T1
   LEFT JOIN CTE_TodaysStaff AS T2
        ON T1.StaffID = T2.StaffID
   WHERE T1.CensusDate = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
       AND T2.StaffID IS NULL
),
CTE_NewToday AS
(
   SELECT DISTINCT T1.StaffID
   FROM CTE_TodaysStaff AS T1
   LEFT JOIN table1 AS T2
       ON T1.StaffID = T2.StaffID
       AND T2.CensusDate < CAST(GETDATE() AS DATE)
   WHERE T2.StaffID IS NULL
)

SELECT T1.StaffID, T1.StaffNAME, T1.CensusDate, 
    CAST(CASE WHEN T2.StaffID IS NULL THEN 0 ELSE 1 END AS BIT) AS IsNotShowingToday,
    CAST(CASE WHEN T3.StaffID IS NULL THEN 0 ELSE 1 END AS BIT) AS IsNewToday
FROM table1 AS T1
LEFT JOIN CTE_NotShowingToday AS T2
   ON T1.StaffID = T2.StaffID
LEFT JOIN CTE_NewToday AS T3
   ON T1.StaffID = T3.StaffID

引用自：https://dba.stackexchange.com/questions/286704