SQL Query - Get Start/End Date per Employment Period

• kburata

  SSC Enthusiast

  Points: 172

  More actions

  February 10, 2021 at 4:16 am

  #3843196

  Hi All, Need to get Start and End Date per eployment period, meaning an employee can be hired and rehired.

  I attached a sample data and desired output.

  

   

  Thanks

• Ken McKelvey

  SSCoach

  Points: 18765

  More actions

  February 10, 2021 at 12:23 pm

  #3843327

  You should provide consumable test data with dates in ISO format:

  SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
  GO
  CREATE TABLE #t
  (
  EmployeeID varchar(20) NOT NULL
  ,StartDate date NOT NULL
  ,EndDate date NULL
  ,LeavingReason nvarchar(80) NULL
  ,PRIMARY KEY (EmployeeID, StartDate)
  );
  INSERT INTO #t
  VALUES ('100001', '20190204', '20200906', NULL)
  ,('100001', '20200907', '20201108', NULL)
  ,('100001', '20201109', '20210108', 'End of Contract')
  ,('100001', '20210201', NULL, NULL);

  The following will work with the given data:

  WITH Gaps
  AS
  (
  SELECT EmployeeID, StartDate, EndDate, LeavingReason
  ,CASE
  WHEN StartDate = COALESCE(DATEADD(day, 1, LAG(EndDate) OVER (PARTITION BY EmployeeID ORDER BY StartDate)), '19000101')
  THEN 0
  ELSE 1
  END AS Gap
  FROM #t
  )
  ,Grps
  AS
  (
  SELECT EmployeeID, StartDate, EndDate, LeavingReason
  ,SUM(Gap) OVER (PARTITION BY EmployeeID ORDER BY StartDate) AS Grp
  FROM Gaps
  )
  SELECT EmployeeID
  ,MIN(StartDate) AS StartDate
  ,MAX(EndDate) AS EndDate
  ,MAX(LeavingReason) AS LeavingReason
  FROM Grps
  GROUP BY EmployeeID, Grp;

  • This reply was modified 3 years, 2 months ago by  Ken McKelvey.