如何筛选特定时间段内全程在办公地点的员工？（SQL及DataFrame场景）

Got it, let's work through this problem. We need to find employees who were in an office every single day of July 2010—no home stays at all during that month. Here are solutions using both SQL and Pandas, since you mentioned either approach is acceptable:

Approach #

First, we'll identify employees who had any home stay overlapping with July 2010—these are the ones we need to exclude. Then, we'll pull all location records for the remaining employees that overlap with July 2010 (since these employees have no home stays, all their overlapping records will be office locations).

Code #

-- CTE to get IDs of employees who had a home stay in July 2010
WITH excluded_employees AS (
    SELECT DISTINCT ID
    FROM employee_locations
    WHERE location = 'Home'
      -- Check if the home stay overlaps with July 2010
      AND date_start <= '2010-07-31'
      AND date_end >= '2010-07-01'
)
-- Fetch records for employees NOT in the excluded list, with July-overlapping office stays
SELECT el.ID, el.date_start, el.date_end, el.location
FROM employee_locations el
LEFT JOIN excluded_employees ee ON el.ID = ee.ID
WHERE ee.ID IS NULL
  -- Only include records that overlap with July 2010
  AND el.date_start <= '2010-07-31'
  AND el.date_end >= '2010-07-01'
ORDER BY el.ID, el.date_start;

Explanation #

• The excluded_employees CTE captures any employee whose home stay period overlaps even partially with July 2010.
• The main query uses a LEFT JOIN to exclude those employees, then filters for records that overlap with July 2010. This gives us exactly the office location records for employees who were never home during the month.

Approach #

Similar to the SQL method: first convert date columns to datetime for easy comparison, identify employees with July home stays to exclude, then filter the original DataFrame to keep only valid employees' July-overlapping records.

Code #

import pandas as pd

# First, ensure date columns are datetime objects
df['date_start'] = pd.to_datetime(df['date_start'])
df['date_end'] = pd.to_datetime(df['date_end'])

# Define the target date range for July 2010
july_start = pd.Timestamp('2010-07-01')
july_end = pd.Timestamp('2010-07-31')

# Get IDs of employees who had a home stay overlapping with July 2010
excluded_ids = df[
    (df['location'] == 'Home') &
    (df['date_start'] <= july_end) &
    (df['date_end'] >= july_start)
]['ID'].unique()

# Filter for valid employees and their July-overlapping records
result = df[
    ~df['ID'].isin(excluded_ids) &
    (df['date_start'] <= july_end) &
    (df['date_end'] >= july_start)
].sort_values(by=['ID', 'date_start']).reset_index(drop=True)

# Print or use the result
print(result)

Explanation #

• Converting dates to datetime lets us use Pandas' built-in time comparison logic.
• excluded_ids captures any employee with a home stay that touches July 2010.
• The final filter keeps only employees not in excluded_ids, and their records that overlap with July. Sorting by ID and start date makes the output clean and consistent with your expected result.

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