after.sql

SELECT
    R.UNIT_NUMBER
   ,R.PERIOD
   ,R.COMPANY
   ,R.PROJECT_NUM
   ,V.PROJECT_NAME
   ,A.RESPONSE
   ,R.ASSIGNED_TO_USER
   ,R.DEPR_DATE
FROM main_table R
JOIN main_table A
  ON R.PROJECT_NUM = A.PROJECT_NUM
 AND R.UNIT_NUMBER = A.UNIT_NUMBER
 AND R.COMPANY     = A.COMPANY
 AND R.DEPT_ID     = A.DEPT_ID
JOIN description_table V
  ON R.PROJECT_NUM = V.AGENDA_NUMBER
WHERE R.COMPANY           = @Company
  AND R.DEPT_ID           = @DeptID
  AND R.ASSIGNED_TO_USER <> ''
  AND R.SPLIT_FLAG       <> 'S'
  AND R.PERIOD = (
   SELECT MAX(PERIOD)
   FROM master_table M
   WHERE M.PERIOD < A.PERIOD
  )
  
  AND A.PERIOD           = @CurrentPeriod
  AND A.ASSIGNED_TO_USER = ''

before.sql

--Pseudo-ish Code
@PreviousPeriod =
  SELECT M.PERIOD
  FROM master_table M
  WHERE M.COMPANY = @Company
   AND M.PERIOD < @CurrentPeriod
  ORDER BY PERIOD DESC
  FETCH FIRST 1 ROW ONLY;

SELECT
   R.UNIT_NUMBER
  ,R.PERIOD
  ,R.COMPANY
  ,R.PROJECT_NUM
  ,V.PROJECT_NAME
  ,A.RESPONSE
  ,R.ASSIGNED_TO_USER
  ,R.DEPR_DATE
FROM main_table R
INNER JOIN (
  SELECT 
     RESPONSE
    ,UNIT_NUMBER
    ,PROJECT_NUM
  FROM main_table
  WHERE PERIOD = @CurrentPeriod
    AND COMPANY = @Company
    AND DEPT_ID = @DeptID
) AS A
  ON R.PROJECT_NUM = A.PROJECT_NUM
 AND R.UNIT_NUMBER = A.UNIT_NUMBER
INNER JOIN description_table V
  ON R.PROJECT_NUM = V.AGENDA_NUMBER
WHERE R.PERIOD = @PreviousPeriod
  AND R.DEPT_ID = @DeptID
  AND R.COMPANY = @Company
  AND R.ASSIGNED_TO_USER <> '        '
  AND R.SPLIT_FLAG <> 'S '
  AND R.UNIT_NUMBER IN (
    SELECT C.UNIT_NUMBER
    FROM main_table C
    WHERE C.PERIOD = @CurrentPeriod
      AND C.ASSIGNED_TO_USER = '        '
      AND C.DEPT_ID = @DeptID
  );