sql get newly added customer and customer who left track from one historical table

Question

I have a Staff_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 with todaysDate or staff 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  left-not showing today
ID5     Staff5  03/03/2021  New staff-is newly added today
ID6     Staff6  02/03/2021  
ID6     Staff6  03/03/2021  
ID7     Staff7  02/03/2021  
ID7     Staff7  03/03/2021  

StaffID is float the id may be just ID123, or ID123-2 I have the following script but i get 'error converting data type nvarchar to float'

WITH CTE_TodaysStaff AS
(
    SELECT DISTINCT T1.StaffID
    FROM Staff_table AS T1
    WHERE T1.CensusDate = CAST(GETDATE() AS DATE)
),
CTE_NotShowingToday AS
(
    SELECT DISTINCT T1.StaffID
    FROM Staff_Table 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 Staff_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 Staff_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

appreciate any help, thanks

Answer 1

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)

You can use aggregation:

select staffid,
       max(censusdate),
       (case when max(censusdate) = cast(getdate() as date) then 'New' else 'Removed' end) as which
from staff_table st
where censusdate >= dateadd(day, -1, cast(getdate() as date))
group by staffid
having count(*) = 1

What is the logic? This chooses only the last two days of data. It then aggregates by staffid and selects only those that were present on one date.