如何用SQL生成ERP Status列？需处理varchar转int转换

需求梳理 #

以下是生成ERP Status列的核心规则：

• 当ERP天数＜15天时，拆分「15天以内」账龄为两个子区间：
  • ERP内：0-ERP天
  • ERP外：ERP+1至15天
• 当ERP天数＞15天时，所有≤ERP天数的账龄区间均标记为「ERP内」
• 新增16-45天账龄区间（从原31-60区间拆分），仅适用于ERP天数为45天的欺诈账户
• 支票及未核销账户特殊规则：
  • 贷方交易：ERP天数固定为180天，单独标记
  • 借方交易：ERP天数固定为1天，单独标记
  • 未核销贷方账户：仅借方交易有ERP，所有贷方交易归入「ERP内」
• 需完成varchar类型的ERP天数字段到int类型的转换

实现代码 #

假设数据表包含以下字段：aging_range（账龄区间，varchar）、erp_days（ERP天数，varchar）、account_type（账户类型，varchar）、transaction_type（交易类型，varchar）、is_fraud（是否欺诈，bit/int），SQL实现如下：

SELECT
    -- 转换ERP天数为int类型，TRY_CAST避免非数字值导致报错
    TRY_CAST(erp_days AS INT) AS erp_days_int,
    aging_range,
    account_type,
    transaction_type,
    is_fraud,
    -- 生成ERP Status列
    CASE
        -- 优先处理支票/未核销账户的特殊规则
        WHEN account_type IN ('支票账户', '未核销账户') THEN
            CASE
                WHEN transaction_type = '贷方' THEN 'ERP内（贷方交易ERP=180天）'
                WHEN transaction_type = '借方' THEN 'ERP内（借方交易ERP=1天）'
                ELSE 'ERP内'
            END
        -- 处理未核销贷方账户的贷方交易
        WHEN account_type = '未核销贷方账户' AND transaction_type = '贷方' THEN 'ERP内'
        -- 处理欺诈账户且ERP=45天的新增区间
        WHEN is_fraud = 1 AND TRY_CAST(erp_days AS INT) = 45 THEN
            CASE
                WHEN aging_range = '31-60天' THEN 'ERP内（16-45天）'
                -- 账龄≤30天直接归为ERP内
                WHEN TRY_CAST(LEFT(aging_range, CHARINDEX('天', aging_range)-1) AS INT) <= 30 THEN 'ERP内'
                ELSE 'ERP外'
            END
        -- 处理ERP天数<15天的情况
        WHEN TRY_CAST(erp_days AS INT) < 15 THEN
            CASE
                WHEN aging_range = '15天以内' THEN
                    -- 拆分15天以内的子区间
                    CASE
                        WHEN TRY_CAST(LEFT(aging_range, CHARINDEX('天', aging_range)-1) AS INT) <= TRY_CAST(erp_days AS INT) 
                        THEN CONCAT('ERP内（0-', erp_days, '天）')
                        ELSE CONCAT('ERP外（', CAST(TRY_CAST(erp_days AS INT)+1 AS VARCHAR), '-15天）')
                    END
                -- 其他账龄区间判断是否在ERP内
                WHEN TRY_CAST(LEFT(aging_range, CHARINDEX('天', aging_range)-1) AS INT) <= TRY_CAST(erp_days AS INT) 
                THEN 'ERP内'
                ELSE 'ERP外'
            END
        -- 处理ERP天数>15天的情况
        WHEN TRY_CAST(erp_days AS INT) > 15 THEN
            CASE
                WHEN TRY_CAST(LEFT(aging_range, CHARINDEX('天', aging_range)-1) AS INT) <= TRY_CAST(erp_days AS INT) 
                THEN 'ERP内'
                ELSE 'ERP外'
            END
        -- 默认情况
        ELSE 'ERP内'
    END AS ERP_Status
FROM your_table_name;

关键说明 #

1. 类型转换：使用TRY_CAST而非CAST，避免因erp_days字段存在非数字值导致查询中断；若需强制转换，可替换为CAST(erp_days AS INT)并提前清理脏数据
2. 规则优先级：特殊账户规则、欺诈账户规则放在最前面，确保不会被通用规则覆盖
3. 账龄区间处理：通过LEFT和CHARINDEX截取账龄区间的数字部分，实现区间范围的判断
4. 动态拼接：使用CONCAT函数动态生成区间描述文本，适配不同的ERP天数

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