基于起止日期表获取员工最新雇佣/重雇日期的SQL查询需求

Problem Statement #

We have an employment records table with the following structure and data:

We need to write a SQL query that returns the latest hire date for each person, where a "hire date" is either the initial start date or the start date of a new employment period after an interruption (a gap of at least one day between the previous end date and current start date). The expected output is:

Approach #

To solve this, use window functions to identify gaps in employment and flag the start of new hire periods:

1. Use the LAG() function to get the end date of the previous employment period for each person.
2. Flag rows where either it's the first employment record or there's a gap of at least one day between the previous end date and current start date. These rows represent hire events.
3. For each person, select the maximum start date from the flagged hire events to get the latest hire date.

SQL Query #

WITH HireEvents AS (
    SELECT 
        Person,
        StartDate,
        LAG(EndDate) OVER (PARTITION BY Person ORDER BY StartDate) AS PreviousEndDate
    FROM Employment
),
FilteredHireDates AS (
    SELECT 
        Person,
        StartDate
    FROM HireEvents
    WHERE 
        PreviousEndDate IS NULL 
        OR StartDate > DATEADD(DAY, 1, PreviousEndDate)
)
SELECT 
    Person,
    MAX(StartDate) AS [Latest Hire Date]
FROM FilteredHireDates
GROUP BY Person;

Explanation #

1. HireEvents CTE: This uses LAG() to retrieve the end date of the prior employment period for each row, partitioned by person and ordered by start date. This allows comparison of consecutive periods.
2. FilteredHireDates CTE: This filters rows to only include hire events:
   • The first row for each person (where PreviousEndDate is NULL) is always a hire event.
   • Rows where the current start date is more than one day after the previous end date indicate an interruption, so this start date is a new hire event.
3. Final SELECT: Groups by person and selects the maximum start date from the filtered hire events, which gives the latest hire date (either initial hire or the start of the most recent period after an interruption).

Notes #

• This query uses SQL Server syntax for DATEADD(). For other SQL dialects:
  • PostgreSQL: Replace DATEADD(DAY, 1, PreviousEndDate) with PreviousEndDate + INTERVAL '1 day'
  • MySQL: Replace DATEADD(DAY, 1, PreviousEndDate) with DATE_ADD(PreviousEndDate, INTERVAL 1 DAY)

内容的提问来源于stack exchange，提问作者ssjklaki