如何在SQL Server中基于两表计算最后结束日期与起始日期的天数差

Got it, let's work through this problem step by step. First, I’ll make a reasonable assumption since you didn’t specify the linking column: both T1 and T2 share a common id column that connects each start date in T1 to its corresponding records in T2. That’s the key to matching the right dates together.

Since T2 has multiple end dates for the same ID, we first need to grab the most recent (last) end date for each ID. The simplest way to do this is with a GROUP BY query:

SELECT id, MAX(end_date) AS latest_end_date
FROM T2
GROUP BY id

This gives us a result set where each ID maps to its latest end date. If you need to include other columns from T2 alongside the latest end date, you could use a window function like ROW_NUMBER() instead, but for this problem, GROUP BY is more straightforward.

Next, we join this filtered T2 data with T1 to pair each start date with its matching latest end date, then calculate the number of days between them. The exact function for date difference varies by database, so here are examples for the most common ones:

MySQL/MariaDB #

Use the DATEDIFF() function, which takes the end date first, then the start date:

SELECT 
    t1.id,
    t1.start_date,
    t2_latest.latest_end_date,
    DATEDIFF(t2_latest.latest_end_date, t1.start_date) AS day_difference
FROM T1
JOIN (
    SELECT id, MAX(end_date) AS latest_end_date
    FROM T2
    GROUP BY id
) AS t2_latest ON t1.id = t2_latest.id;

PostgreSQL #

PostgreSQL lets you subtract dates directly to get an integer day difference, or use AGE() if you want a more readable interval (then extract the day count):

SELECT 
    t1.id,
    t1.start_date,
    t2_latest.latest_end_date,
    -- Direct subtraction gives integer days
    (t2_latest.latest_end_date - t1.start_date) AS day_difference,
    -- Alternatively, use AGE for interval format, then extract days
    DATE_PART('day', AGE(t2_latest.latest_end_date, t1.start_date)) AS day_difference_from_age
FROM T1
JOIN (
    SELECT id, MAX(end_date) AS latest_end_date
    FROM T2
    GROUP BY id
) AS t2_latest ON t1.id = t2_latest.id;

SQL Server #

Use DATEDIFF() with the day parameter, passing the start date first, then the end date:

SELECT 
    t1.id,
    t1.start_date,
    t2_latest.latest_end_date,
    DATEDIFF(day, t1.start_date, t2_latest.latest_end_date) AS day_difference
FROM T1
JOIN (
    SELECT id, MAX(end_date) AS latest_end_date
    FROM T2
    GROUP BY id
) AS t2_latest ON t1.id = t2_latest.id;

• If there are IDs in T1 that don’t have any matching records in T2, the JOIN will exclude them. To keep all T1 records (with NULL for missing T2 data), replace JOIN with LEFT JOIN. You can use COALESCE to turn those NULL differences into a default value like 0:

  -- Example for MySQL
  COALESCE(DATEDIFF(t2_latest.latest_end_date, t1.start_date), 0) AS day_difference
  

• If your datetime columns include time parts but you only care about date-based differences, cast them to DATE first (e.g., CAST(t1.start_date AS DATE)).

内容的提问来源于stack exchange，提问作者Md Kamran Azam