SQL - New Hires and Attrition (separate table)

sql-new-hires-attrition-new-table.sql

DROP TABLE IF EXISTS #previous
DROP TABLE IF EXISTS #current
 
DECLARE @previousDate AS DATE;
SET @previousDate = '9/30/2021';
 
DECLARE @currentDate AS DATE;
SET @currentDate = '10/31/2021';
 
 
-- Create roster tables
SELECT report_date, employee_id, employee_name
INTO #previous
FROM dbo.Rosters
WHERE report_date = @previousDate
 
SELECT report_date, employee_id, employee_name
INTO #current
FROM dbo.Rosters
WHERE report_date = @currentDate
 
 
 
 
-- New Hires
INSERT INTO dbo.MonthlyTracking (
       the_month, employee_id, employee_name,
       category, the_value
)
 
       SELECT
              tblCurrent.report_date, tblCurrent.employee_id, tblCurrent.employee_name,
              'New Hire' AS category, 1
       FROM #current tblCurrent
       LEFT JOIN #previous tblPrevious ON
              tblPrevious.employee_id = tblCurrent.employee_id
       WHERE
              tblPrevious.employee_id IS NULL
      
 
 
 
 
-- Attrition
INSERT INTO dbo.MonthlyTracking (
       the_month, employee_id, employee_name,
       category, the_value
)
 
       SELECT
              @currentDate, tblPrevious.employee_id, tblPrevious.employee_name,
              'Attrition' AS category, -1
       FROM #previous tblPrevious
       LEFT JOIN #current tblCurrent ON
              tblCurrent.employee_id = tblPrevious.employee_id
       WHERE
              tblCurrent.employee_id IS NULL