如何编写SQL按申报周期规则判断款项是否按时缴纳并输出Y/N标识

需求说明 #

编写SQL判定款项是否按时支付，输出OnTime Payment字段，取值为Y/N，判定规则如下：

• 月度申报：需在申报周期次月19日前完成支付，示例：1月月度申报（Jan-Jan）支付截止日为2月19日
• 非年末季度申报：需在申报周期后第2个月16日前完成支付，示例：1-3月季度申报（Jan-Mar）支付截止日为5月16日
• 年末季度（10-12月）申报：需在申报周期次月30日前完成支付，示例：10-12月季度申报（Oct-Dec）支付截止日为次年1月30日

测试数据集（修正语法错误后） #

with data as (
    select 'Sep-Sep2021' AS PERIOD,
           '2021-10-11 22:09:45' AS DT,
           'PAID' AS STATUS
    union all
    select 'Sep-Sep2021' AS PERIOD,
           '2021-10-20 22:09:45' AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Mar2021'AS PERIOD,
           '2021-04-11 22:09:45'AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Mar2021'AS PERIOD,
           '2021-05-20 22:09:45'AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Jan2021'AS PERIOD,
           '2021-02-11 22:09:44'AS DT,
           'PAID'AS STATUS
    union all
    select 'Sep-Sep2021'AS PERIOD,
           '2021-10-12 04:10:00'AS DT,
           'CANCELLED'AS STATUS
    union all 
    select 'Jul-Sep2021'AS PERIOD,
           '2021-10-12 04:10:00'AS DT,
           'PAID' AS STATUS
    union all 
    select 'Oct-Dec2021'AS PERIOD,
           '2022-01-29 04:10:00'AS DT,
           'PAID' AS STATUS
)
select * from data;

注：原测试数据存在两处语法错误：Jul-Sep2021行的PAID后缺AS关键字、Oct-Dec2021行的PAID缺右引号，已修正。

完整实现SQL（MySQL语法兼容） #

with data as (
    select 'Sep-Sep2021' AS PERIOD,
           '2021-10-11 22:09:45' AS DT,
           'PAID' AS STATUS
    union all
    select 'Sep-Sep2021' AS PERIOD,
           '2021-10-20 22:09:45' AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Mar2021'AS PERIOD,
           '2021-04-11 22:09:45'AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Mar2021'AS PERIOD,
           '2021-05-20 22:09:45'AS DT,
           'PAID' AS STATUS
    union all
    select 'Jan-Jan2021'AS PERIOD,
           '2021-02-11 22:09:44'AS DT,
           'PAID'AS STATUS
    union all
    select 'Sep-Sep2021'AS PERIOD,
           '2021-10-12 04:10:00'AS DT,
           'CANCELLED'AS STATUS
    union all 
    select 'Jul-Sep2021'AS PERIOD,
           '2021-10-12 04:10:00'AS DT,
           'PAID' AS STATUS
    union all 
    select 'Oct-Dec2021'AS PERIOD,
           '2022-01-29 04:10:00'AS DT,
           'PAID' AS STATUS
),
parse_data AS (
    SELECT 
        *,
        SUBSTRING_INDEX(PERIOD, '-', 1) AS start_month,
        SUBSTRING_INDEX(SUBSTRING_INDEX(PERIOD, '-', -1), 1,3) AS end_month,
        RIGHT(PERIOD,4) AS period_year,
        STR_TO_DATE(DT, '%Y-%m-%d %H:%i:%s') AS pay_dt
    FROM data
),
calc_deadline AS (
    SELECT 
        *,
        CASE 
            -- 月度申报：次月19号
            WHEN start_month = end_month THEN DATE_ADD(DATE_ADD(STR_TO_DATE(CONCAT(period_year, end_month), '%Y%b'), INTERVAL 1 MONTH), INTERVAL 18 DAY)
            -- 年末季度申报：次年1月30号
            WHEN end_month = 'Dec' AND start_month = 'Oct' THEN DATE_ADD(DATE_ADD(STR_TO_DATE(CONCAT(period_year, end_month), '%Y%b'), INTERVAL 1 MONTH), INTERVAL 29 DAY)
            -- 普通季度申报：结束月后第2个月16号
            ELSE DATE_ADD(DATE_ADD(STR_TO_DATE(CONCAT(period_year, end_month), '%Y%b'), INTERVAL 2 MONTH), INTERVAL 15 DAY)
        END AS payment_deadline
    FROM parse_data
)
SELECT 
    PERIOD,
    DT,
    STATUS,
    CASE 
        WHEN STATUS != 'PAID' THEN 'N'
        WHEN pay_dt < payment_deadline THEN 'Y'
        ELSE 'N'
    END AS `OnTime Payment`
FROM calc_deadline;

执行结果 #

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