如何用T-SQL计算首次呼叫解决率(FCR)？求逻辑修正帮助

Hey there! Let’s work through this FCR calculation issue you’re facing. I’ve helped lots of developers build this exact logic, so let’s break down what’s needed and fix any common pitfalls you might have hit.

First, let’s restate the rule to make sure we’re aligned: For every call record, if the same phone number has any subsequent call within 24 hours after the current call’s start time, mark FCR as 0 (no resolution on first call). If there are no such follow-up calls, mark FCR as 1 (resolved on first call).

Core Logic & Query Examples #

The key here is using a subquery to check for matching follow-up calls. EXISTS is ideal here because it stops searching as soon as it finds a match, making it more efficient than counting all matching records.

MySQL Example

SELECT
    phone_number,
    call_start_datetime,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_call_table t2
            WHERE t2.phone_number = t1.phone_number
              -- Ensure we're checking calls that happen AFTER the current one
              AND t2.call_start_datetime > t1.call_start_datetime
              -- Limit to calls within 24 hours of the current call's start
              AND t2.call_start_datetime <= DATE_ADD(t1.call_start_datetime, INTERVAL 24 HOUR)
        ) THEN 0
        ELSE 1
    END AS FCR
FROM your_call_table t1;

PostgreSQL Example

SELECT
    phone_number,
    call_start_datetime,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_call_table t2
            WHERE t2.phone_number = t1.phone_number
              AND t2.call_start_datetime > t1.call_start_datetime
              AND t2.call_start_datetime <= t1.call_start_datetime + INTERVAL '24 hours'
        ) THEN 0
        ELSE 1
    END AS FCR
FROM your_call_table t1;

SQL Server Example

SELECT
    phone_number,
    call_start_datetime,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM your_call_table t2
            WHERE t2.phone_number = t1.phone_number
              AND t2.call_start_datetime > t1.call_start_datetime
              AND t2.call_start_datetime <= DATEADD(HOUR, 24, t1.call_start_datetime)
        ) THEN 0
        ELSE 1
    END AS FCR
FROM your_call_table t1;

Common Pitfalls to Check #

If your original query wasn’t working, these are the most likely issues:

• Checking the wrong time direction: Accidentally looking for calls before the current one instead of after (missing t2.call_start_datetime > t1.call_start_datetime).
• Including the current record: Forgetting to exclude the current call from the subquery (using >= instead of > would count the same call as a follow-up).
• Incorrect time interval syntax: Using the wrong function or parameter for adding 24 hours (e.g., mixing up DATE_ADD arguments in MySQL).

Adding the FCR Column to Your Table #

If you want to permanently add the FCR column to your table (instead of just selecting it), here’s how to do it in MySQL:

-- Step 1: Add the new column
ALTER TABLE your_call_table ADD COLUMN FCR TINYINT;

-- Step 2: Populate the column with values
UPDATE your_call_table t1
SET FCR = CASE
    WHEN EXISTS (
        SELECT 1
        FROM your_call_table t2
        WHERE t2.phone_number = t1.phone_number
          AND t2.call_start_datetime > t1.call_start_datetime
          AND t2.call_start_datetime <= DATE_ADD(t1.call_start_datetime, INTERVAL 24 HOUR)
    ) THEN 0
    ELSE 1
END;

Just replace your_call_table with your actual table name, and adjust the time interval syntax if you’re using a different database.

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