SQL查询丢失的记录

您可以使用递归CTE按月展开它们。

例如。

样本数据：

CREATE TABLE YourEmployeeDataTable
(
  MASKID VARCHAR(16) NOT NULL, 
  Status VARCHAR(10) NOT NULL, 
  Reason VARCHAR(30) NOT NULL, 
  Action VARCHAR(30) NOT NULL, 
  Start_Date DATE NOT NULL, 
  MonthDiff INT
);

INSERT INTO YourEmployeeDataTable
(MASKID, Status, Reason, Action, Start_Date, MonthDiff) VALUES
  ('AAC1000632860QSG', 'Active', 'Hire001',   'Hire',        '2008-04-01', 23)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2010-03-31', 1)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2010-04-01', 0)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2010-04-29', 2)
, ('AAC1000632860QSG', 'Active', 'TRANSFER01', 'Transfer',   '2010-06-01', 2)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2010-08-01', 1)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2010-09-01', 4)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2011-01-01', 3)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2011-04-01', 4)
, ('AAC1000632860QSG', 'Active', 'DATACHG01', 'Data Change', '2011-08-01', 12)
, ('AAC1000632860QSG', 'Separated', 'YC',     'Separation',  '2012-08-01', null)
查询：

WITH RCTE_EMP_DATES AS
(
  SELECT MASKID, Status, Reason, Action
  , Start_Date AS startDate
  , LEAD(Start_Date,1,Start_Date) OVER (PARTITION BY MASKID ORDER BY Start_Date) AS endDate
  , IIF(MonthDiff>0,1,0) AS MonthDiff
  , 0 AS lvl
  FROM YourEmployeeDataTable

  UNION ALL

  SELECT MASKID, Status, null, null
  , DATEADD(DAY,1,EOMONTH(DATEADD(month,1,startDate),-1))
  , endDate
  , 1
  , lvl + 1
  FROM RCTE_EMP_DATES
  WHERE startDate < DATEADD(month,-1,endDate)
)
SELECT * 
FROM RCTE_EMP_DATES
ORDER BY MASKID, startDate