SQL Server中匹配差异ID并计算工作日天数差的查询需求

Alright, let's tackle this problem head-on: we need to match IDs between two tables, calculate working day differences (excluding weekends and holidays), and wrap it all into one SQL query. Here's how to do it:

First: Fix the ID Mismatch #

The key hurdle is normalizing the TX table's IDs to match TX_External. Since TX.ID has a random suffix after the second underscore (like AB_123456_ABC), we need to extract everything up to that second underscore to get AB_123456.

ID Normalization by Database: #

• MySQL/MariaDB: Use SUBSTRING_INDEX twice to grab the first two segments:

  SUBSTRING_INDEX(TX.ID, '_', 2) AS Normalized_ID
  

• SQL Server: Combine CHARINDEX to find the second underscore's position, then slice the string:

  SUBSTRING(TX.ID, 1, CHARINDEX('_', TX.ID, CHARINDEX('_', TX.ID) + 1) - 1) AS Normalized_ID
  

• Oracle: Use INSTR to locate the second underscore, then SUBSTR to extract the relevant part:

  SUBSTR(TX.ID, 1, INSTR(TX.ID, '_', 1, 2) - 1) AS Normalized_ID
  

Second: Calculate Working Days (Excluding Weekends & Holidays) #

To get the correct working day count, we need to:

1. Compute total days between the two dates
2. Subtract weekends (Saturdays and Sundays)
3. Subtract any public holidays in the date range (we'll assume you have a Holiday_Table with a Holiday_Date column for this)

Working Day Calculation by Database: #

MySQL/MariaDB:

DATEDIFF(te.Date_Received, tx.Date_Reported) 
  -- Subtract full weekends between the dates
  - (WEEKDAY(te.Date_Received) - WEEKDAY(tx.Date_Reported)) DIV 7 * 2 
  -- Subtract partial weekend days if the start/end date falls on a weekend
  - CASE WHEN WEEKDAY(tx.Date_Reported) >= 5 THEN 5 - WEEKDAY(tx.Date_Reported) ELSE 0 END 
  - CASE WHEN WEEKDAY(te.Date_Received) >= 5 THEN WEEKDAY(te.Date_Received) - 4 ELSE 0 END 
  -- Subtract public holidays
  - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
AS Days_Taken

SQL Server:

DATEDIFF(DAY, tx.Date_Reported, te.Date_Received)
  -- Subtract full weekends
  - (DATEDIFF(WEEK, tx.Date_Reported, te.Date_Received) * 2)
  -- Subtract weekend days if start/end is on Saturday/Sunday
  - CASE WHEN DATEPART(WEEKDAY, tx.Date_Reported) IN (1,7) THEN 1 ELSE 0 END
  - CASE WHEN DATEPART(WEEKDAY, te.Date_Received) IN (1,7) THEN 1 ELSE 0 END
  -- Subtract public holidays
  - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
AS Days_Taken

Oracle:

(TRUNC(te.Date_Received) - TRUNC(tx.Date_Reported)) 
  -- Subtract full weekends (using ISO week start)
  - ((TRUNC(te.Date_Received, 'IW') - TRUNC(tx.Date_Reported, 'IW')) / 7) * 2 
  -- Subtract weekend days if start/end is Sat/Sun
  - CASE WHEN TO_CHAR(tx.Date_Reported, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 ELSE 0 END 
  - CASE WHEN TO_CHAR(te.Date_Received, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 ELSE 0 END 
  -- Subtract public holidays
  - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
AS Days_Taken

Full Single Query (By Database) #

Now let's combine everything into a single query that joins the tables, normalizes IDs, and calculates working days:

MySQL/MariaDB: #

SELECT 
  te.ID AS `Customer ID`,
  DATEDIFF(te.Date_Received, tx.Date_Reported) 
    - (WEEKDAY(te.Date_Received) - WEEKDAY(tx.Date_Reported)) DIV 7 * 2 
    - CASE WHEN WEEKDAY(tx.Date_Reported) >= 5 THEN 5 - WEEKDAY(tx.Date_Reported) ELSE 0 END 
    - CASE WHEN WEEKDAY(te.Date_Received) >= 5 THEN WEEKDAY(te.Date_Received) - 4 ELSE 0 END 
    - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
  AS Days_Taken
FROM TX tx
JOIN TX_External te ON SUBSTRING_INDEX(tx.ID, '_', 2) = te.ID;

SQL Server: #

SELECT 
  te.ID AS [Customer ID],
  DATEDIFF(DAY, tx.Date_Reported, te.Date_Received)
    - (DATEDIFF(WEEK, tx.Date_Reported, te.Date_Received) * 2)
    - CASE WHEN DATEPART(WEEKDAY, tx.Date_Reported) IN (1,7) THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(WEEKDAY, te.Date_Received) IN (1,7) THEN 1 ELSE 0 END
    - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
  AS Days_Taken
FROM TX tx
JOIN TX_External te ON SUBSTRING(tx.ID, 1, CHARINDEX('_', tx.ID, CHARINDEX('_', tx.ID) + 1) - 1) = te.ID;

Oracle: #

SELECT 
  te.ID AS "Customer ID",
  (TRUNC(te.Date_Received) - TRUNC(tx.Date_Reported)) 
    - ((TRUNC(te.Date_Received, 'IW') - TRUNC(tx.Date_Reported, 'IW')) / 7) * 2 
    - CASE WHEN TO_CHAR(tx.Date_Reported, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 ELSE 0 END 
    - CASE WHEN TO_CHAR(te.Date_Received, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN 1 ELSE 0 END 
    - (SELECT COUNT(*) FROM Holiday_Table ht WHERE ht.Holiday_Date BETWEEN tx.Date_Reported AND te.Date_Received)
  AS Days_Taken
FROM TX tx
JOIN TX_External te ON SUBSTR(tx.ID, 1, INSTR(tx.ID, '_', 1, 2) - 1) = te.ID;

Quick Notes: #

• If you don't have a Holiday_Table, just remove the subquery that counts holidays from the calculation.
• Double-check that your date columns are actual date/time types (not strings) to avoid unexpected errors.
• Test the ID normalization logic with your real data—if some TX.ID values only have one underscore, adjust the logic (but the problem states they have a suffix after the second underscore, so this should work).

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