SQL - Attrition (one query)

sql-attrition.sql

-- Attrition
 
DECLARE @maxDate DATE = (SELECT MAX(ReportDate) FROM dbo.Rosters)
SET @maxDate = CAST( DATEADD(month, DATEDIFF(month, 0, @maxDate), 0) AS DATE)
--SELECT @maxDate
 
SELECT
       tblPrevious.FirstOfMonth, tblPrevious.EmplID, tblPrevious.Name
 
FROM (
       
       SELECT tblPrevious.EmplID, tblPrevious.Name,
              CAST( DATEADD(month, DATEDIFF(month, 0, tblPrevious.ReportDate), 0) AS DATE) AS FirstOfMonth
       FROM dbo.Rosters tblPrevious
 
) AS tblPrevious
LEFT JOIN (
       
       SELECT tblCurrent.EmplID, tblCurrent.Name, tblCurrent.LevelNumber,
              DATEADD( month, -1, CAST( DATEADD(month, DATEDIFF(month, 0, tblCurrent.ReportDate), 0) AS DATE) ) AS FirstOfPrevMonth
       FROM dbo.Rosters tblCurrent
       
) tblCurrent ON
       tblCurrent.EmplID = tblPrevious.EmplID
       AND tblPrevious.FirstOfMonth = tblCurrent.FirstOfPrevMonth
WHERE
           tblCurrent.EmplID IS NULL
       AND tblPrevious.FirstOfMonth < @maxDate -- *all* entries will be there in the maximum month, so exclude that