基于notes表查询项目员工工作时段对应天数的技术问询

Let's break down how to solve this problem effectively. First, let's recap the core requirements to make sure we're aligned:

• We have a notes table with entries marking when employees start/end work on a project.
• Each employee has exactly one start record, but may have multiple end records (we only care about the most recent valid end date).
• We need to calculate total days the project was active, plus counts of days where at least one employee was working, and days where no one was working.

Step 1: Clean Employee Work Periods #

First, we'll create a CTE to extract each employee's valid work window—their unique start date and their most recent end date (ignoring any extra end entries). If an employee hasn't ended their work yet, their end date will be NULL.

PostgreSQL Example: #

WITH employee_work_periods AS (
    SELECT
        employee_id,
        -- Grab the only start date per employee
        MAX(CASE WHEN action = 'start' THEN date::DATE END) AS start_date,
        -- Grab the latest end date (ignore older ones)
        MAX(CASE WHEN action = 'end' THEN date::DATE END) AS end_date
    FROM notes
    GROUP BY employee_id
    -- Ensure we only include employees with a valid start date
    HAVING MAX(CASE WHEN action = 'start' THEN date::DATE END) IS NOT NULL
    -- Optional: Filter out invalid periods where end is before start
    AND (MAX(CASE WHEN action = 'end' THEN date::DATE END) IS NULL 
         OR MAX(CASE WHEN action = 'end' THEN date::DATE END) >= MAX(CASE WHEN action = 'start' THEN date::DATE END))
)

MySQL Example: #

WITH employee_work_periods AS (
    SELECT
        employee_id,
        MAX(CASE WHEN action = 'start' THEN DATE(date) END) AS start_date,
        MAX(CASE WHEN action = 'end' THEN DATE(date) END) AS end_date
    FROM notes
    GROUP BY employee_id
    HAVING MAX(CASE WHEN action = 'start' THEN DATE(date) END) IS NOT NULL
    AND (MAX(CASE WHEN action = 'end' THEN DATE(date) END) IS NULL 
         OR MAX(CASE WHEN action = 'end' THEN DATE(date) END) >= MAX(CASE WHEN action = 'start' THEN DATE(date) END))
)

Step 2: Generate Full Project Date Range #

Next, we need a list of every date from the project's earliest start to its latest end (or today if some employees are still active). This lets us check each day individually.

PostgreSQL Example: #

, project_date_range AS (
    SELECT
        generate_series(
            (SELECT MIN(start_date) FROM employee_work_periods),
            COALESCE((SELECT MAX(end_date) FROM employee_work_periods), CURRENT_DATE),
            INTERVAL '1 day'
        )::DATE AS project_date
    FROM employee_work_periods
)

MySQL Example (Recursive CTE): #

, project_date_range AS (
    SELECT MIN(start_date) AS project_date FROM employee_work_periods
    UNION ALL
    SELECT DATE_ADD(project_date, INTERVAL 1 DAY)
    FROM project_date_range
    WHERE project_date < COALESCE((SELECT MAX(end_date) FROM employee_work_periods), CURDATE())
)

Step 3: Mark Daily Work Status #

Now we'll check each date in our range to see if at least one employee was working that day. We'll label each day as occupied (someone was working) or unoccupied (no one was on the project).

, daily_work_status AS (
    SELECT
        p.project_date,
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM employee_work_periods e
                WHERE e.start_date <= p.project_date
                  AND (e.end_date IS NULL OR e.end_date >= p.project_date)
            ) THEN 'occupied'
            ELSE 'unoccupied'
        END AS status
    FROM project_date_range p
)

Step 4: Calculate Final Counts #

Finally, we'll aggregate the daily statuses to get our desired metrics:

SELECT
    COUNT(*) AS total_project_days,
    SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) AS occupied_days,
    SUM(CASE WHEN status = 'unoccupied' THEN 1 ELSE 0 END) AS unoccupied_days
FROM daily_work_status;

Key Notes & Adjustments #

• Time Truncation: If your date field includes timestamps (not just dates), use DATE(date) (MySQL) or date::DATE (PostgreSQL) to remove time components—this ensures we're comparing full days correctly.
• Custom Date Ranges: If you only want to count days within a specific window (e.g., Q1 2024), add a WHERE clause to the project_date_range CTE to restrict dates to your target period.
• Edge Cases: For employees with no end date, we treat them as still active (so all dates from their start to today count as occupied).

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