如何编写SQL语句，基于房贷文件三种决策日期计算核保处理时长

Got it, let's break down how to solve this problem of calculating the number of days an Underwriter takes to finalize a decision on a mortgage file.

First, let's confirm the core requirements to make sure we're aligned:

• We have a submission date (when the file was marked "submitted to Underwriting")
• Three possible decision date fields: approval_date, suspension_date, and conditional_approval_date (only one of these will be populated per file)
• We need to calculate the days between the submission date and the first non-null decision date present in the system.

Solution Approach #

The key here is to use a function that grabs the first non-null value from our three decision date columns. Most SQL dialects offer COALESCE() for this exact purpose—it returns the first non-null argument you pass to it. Once we have that effective decision date, we just compute the day difference between it and the submission date.

SQL Code Examples #

Below are tailored examples for common database systems. We'll assume your table is named mortgage_files with these columns:

• file_id (unique identifier for the mortgage file)
• submitted_to_underwriting_date (DATE/DATETIME type, non-null once submitted)
• approval_date (DATE/DATETIME type, nullable)
• suspension_date (DATE/DATETIME type, nullable)
• conditional_approval_date (DATE/DATETIME type, nullable)

MySQL/MariaDB

SELECT
    file_id,
    submitted_to_underwriting_date,
    COALESCE(approval_date, suspension_date, conditional_approval_date) AS decision_date,
    DATEDIFF(
        COALESCE(approval_date, suspension_date, conditional_approval_date),
        submitted_to_underwriting_date
    ) AS decision_turnaround_days
FROM mortgage_files
WHERE submitted_to_underwriting_date IS NOT NULL;

PostgreSQL

PostgreSQL handles date differences by subtracting dates (returns an interval), which we can convert to integer days with EXTRACT():

SELECT
    file_id,
    submitted_to_underwriting_date,
    COALESCE(approval_date, suspension_date, conditional_approval_date) AS decision_date,
    EXTRACT(DAY FROM 
        COALESCE(approval_date, suspension_date, conditional_approval_date) - submitted_to_underwriting_date
    ) AS decision_turnaround_days
FROM mortgage_files
WHERE submitted_to_underwriting_date IS NOT NULL;

Alternatively, using AGE() for clearer date interval logic:

SELECT
    file_id,
    submitted_to_underwriting_date,
    COALESCE(approval_date, suspension_date, conditional_approval_date) AS decision_date,
    EXTRACT(DAY FROM AGE(
        COALESCE(approval_date, suspension_date, conditional_approval_date),
        submitted_to_underwriting_date
    )) AS decision_turnaround_days
FROM mortgage_files
WHERE submitted_to_underwriting_date IS NOT NULL;

SQL Server

SQL Server uses DATEDIFF(day, start_date, end_date) to calculate day differences:

SELECT
    file_id,
    submitted_to_underwriting_date,
    COALESCE(approval_date, suspension_date, conditional_approval_date) AS decision_date,
    DATEDIFF(day, 
        submitted_to_underwriting_date,
        COALESCE(approval_date, suspension_date, conditional_approval_date)
    ) AS decision_turnaround_days
FROM mortgage_files
WHERE submitted_to_underwriting_date IS NOT NULL;

Handling Edge Cases #

• If none of the decision dates are populated, COALESCE() returns NULL, and the turnaround days will also be NULL. You can explicitly label these as "Pending" with a CASE statement:

  -- Example for MySQL
  SELECT
      file_id,
      submitted_to_underwriting_date,
      COALESCE(approval_date, suspension_date, conditional_approval_date) AS decision_date,
      CASE
          WHEN COALESCE(approval_date, suspension_date, conditional_approval_date) IS NULL THEN 'Pending'
          ELSE CAST(DATEDIFF(
              COALESCE(approval_date, suspension_date, conditional_approval_date),
              submitted_to_underwriting_date
          ) AS CHAR)
      END AS decision_turnaround_days
  FROM mortgage_files;
  

• Always filter out rows where submitted_to_underwriting_date is NULL unless you need to track incomplete submissions.

This approach works reliably because COALESCE prioritizes the first populated decision date, ensuring we use the actual date the Underwriter finalized their call.

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