SQL - New Hires (one query)

sql-new-hires.sql

-- New Hires
 
DECLARE @minDate DATE = (SELECT MIN(ReportDate) FROM dbo.Rosters)
SET @minDate = CAST( DATEADD(month, DATEDIFF(month, 0, @minDate), 0) AS DATE)
--SELECT @minDate
 
SELECT
       tblPrevious.FirstOfPrevMonth, tblPrevious.EmplID, tblPrevious.Name
 
FROM (

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