T SQL, Get new customer added or deleted from historical table I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or they may have left the company yesterday and not showing on todays list).
I want to know each day which staff wasn't on yesterday list, it is new staff and the staff was showing yesterday but not today because they left company. I have tried the following but no joy please help
my historical table is like below and I need a script to get the rows for ID4 and ID5 only.




StaffID
StaffName
CensusDate





ID1
Staff1
02/03/2021



ID1
Staff1
03/03/2021



ID2
Staff2
02/03/2021



ID2
Staff2
03/03/2021



ID3
Staff3
02/03/2021



ID3
Staff3
03/03/2021



ID4
Staff4
02/03/2021
not showing today


ID5
Staff5
03/03/2021
is newly added today


ID6
Staff6
02/03/2021



ID6
Staff6
03/03/2021



ID7
Staff7
02/03/2021



ID7
Staff7
03/03/2021





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
Not sure I completely understand your intention or the difference between Table1 and Table2.
If you are trying to find just the IDs that only occur once in the last two days.
SELECT StaffID, count(StaffID)
FROM Table1 
WHERE CensusDate &gt;= cast(dateadd(day, -2, getdate()) as date)
Group By StaffID