带日期约束的字段关联：SQL实现助理工时匹配方案求助

Got it, let's tackle this problem step by step. The core challenge here is calculating each assistant's end date (since it's not stored in Table A) and then joining that date range to the daily hours in Table B.

Step 1: Calculate Assistant Date Ranges #

First, we need to generate an end date for each assistant. Since each assistant's term ends the day before the next assistant starts, we can use the LEAD() window function to grab the next assistant's start date, then subtract one day to get the current assistant's end date. For the last assistant, we'll use a fallback value (like today's date or a far-future date) since there's no next assistant.

Step 2: Join to Daily Hours Data #

Once we have the full date ranges for each assistant, we can join Table B's daily dates to these ranges to match each day to the correct assistant.

Full SQL Implementation #

Here's the code that works across most modern databases (with minor adjustments for specific systems):

WITH assistant_periods AS (
    SELECT 
        Assistant,
        Start_Date,
        -- Fetch the next assistant's start date, subtract 1 day for end date
        LEAD(Start_Date) OVER (ORDER BY Start_Date) - INTERVAL '1 day' AS End_Date
    FROM TableA
)
SELECT 
    ap.Assistant,
    b.Date,
    b.Worked_Hours
FROM TableB b
JOIN assistant_periods ap 
    ON b.Date BETWEEN ap.Start_Date AND COALESCE(ap.End_Date, CURRENT_DATE)
ORDER BY b.Date;

Database-Specific Adjustments #

If you're using a database with different date functions, tweak the end date calculation:

• MySQL: Replace INTERVAL '1 day' with INTERVAL 1 DAY, so the line becomes DATE_SUB(LEAD(Start_Date) OVER (ORDER BY Start_Date), INTERVAL 1 DAY) AS End_Date
• SQL Server: Use DATEADD(day, -1, LEAD(Start_Date) OVER (ORDER BY Start_Date)) AS End_Date

Verification with Your Sample Data #

Let's confirm this works with your test data:

• James's period: 07/01/17 to 01/02/18 → matches the first 3 days in Table B (12/31/17, 01/01/18, 01/02/18)
• Frank's period: 01/03/18 to 01/05/18 → matches 01/03/18 to 01/05/18
• Erika's period: 01/06/18 onwards → matches 01/06/18 and 01/07/18

This will output exactly the Assistant | Date | Worked Hours table you need.

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