SQL - Find number of new starters and leavers between snapshot dates-CodePudding

I have an SQL table staff that takes a snapshot on specific dates and adds a row of every staffID and the corresponding DateID.

I need to find out how many staff have joined since the next DateID, and how many have left.

So in the example staff table below at DateID B, StaffID's 002 and 003 from DateID A aren't there so have 'left', and DateID B has staffID's 004,005,006 that were not there in DateID so are 'new'.

StaffID  DateID
007      C
005      C
006      B
005      B
004      B
001      B
003      A
002      A
001      A

I've surmised how these results would appear in the below.

DateID  New  Leavers
A       0    2
B       3    2
C       1    3

My current and only way of solving this is to go through each DateID with the DateID before it and left join the older date counting the rows where the old date it null for the number of new staff and swapping the tables round for the leavers.

SELECT t1.DateID, count(*) AS Total

(SELECT *
FROM staff
WHERE DateID = 'B') t1

LEFT JOIN

(SELECT *
FROM staff
WHERE DateID = 'A') t2

ON t1.StaffID = t2.StaffID
WHERE t2.StaffID is null
GROUP BY t1.DateID

This method is horribly inefficient with a larger table and hoping anyone has ideas for a way to do in one script. Alternatively, a script just for new staff and a script for just leavers would be as good.

As requested by @Larnu, I've added a snapshot table that holds all the DateID's. the staff table is filtered to just show DateID's that are weekly.

DateID Weekly Monthly Yearly
A      Y      Y       N
B      Y      N       N
C      Y      N       N
D      N      N       N
E      Y      Y       N
F      N      N       Y

CodePudding user response：

LEAD and LAG window functions would help here.

Since the DateIDs are not consecutive, you need to calculate LEAD/LAG on that also, and join it

SELECT
  s.DateID,
  [New]   = COUNT(CASE WHEN s.PrevID IS NULL OR s.PrevID <> d.PrevDateID THEN 1 END),
  Leavers = COUNT(CASE WHEN s.NextID IS NULL OR s.NextID <> d.NextDateID THEN 1 END)
FROM (
    SELECT *,
      PrevDateID =  LAG(DateID) OVER (ORDER BY DateID),
      NextDateID = LEAD(DateID) OVER (ORDER BY DateID)
    FROM Dates d
) d
JOIN (
    SELECT *,
      PrevID =  LAG(s.DateID) OVER (PARTITION BY StaffID ORDER BY DateID),
      NextID = LEAD(s.DateID) OVER (PARTITION BY StaffID ORDER BY DateID)
    FROM staff s
) s ON s.DateID = d.DateID
GROUP BY
  s.DateID;