SQL:Check if the date is paid on time before following the return period-CodePudding

SQL: Check if the date is paid before the 19th of the month following the return period

The requirement is to determine OnTime Payment 'Y'/'N'

• If monthly = PAID before the 19th of the month following the return period
Example: Jan-Jan payment deadline = Feb 19
• If quarterly = paid before the 16th of the 2nd month following the return period
Example: Jan-Mar payment deadline = May 16
• If last quarterly = paid before the 30th of the month following the return period
Example: Oct-Dec payment deadline = Jan 30

For quarterly, if it is the last quarter then its paid 30th of the following month, For rest of the quarter month, it is 16th of the month

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'
    union all
    select 'Jan-Mar2021'AS PERIOD,
           '2021-05-20 22:09:45'AS DT,
           'PAID'
    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 STATUS
    union all 
    select 'Oct-Dec2021'AS PERIOD,
           '2022-01-29 04:10:00'AS DT,
           'PAID STATUS
)
select * from data;

Expected Result

CodePudding user response：

select
    period,
    IFF(REGEXP_COUNT(period,SUBSTR(period,1,3)) = 2, 'monthly', 'quaterly') AS payment_type,
    CASE payment_type
        WHEN 'monthly' THEN DATEADD('month', 1, TO_DATE('19'||SUBSTR(period,5), 'DDMonYYYY'))
        WHEN 'quaterly' THEN DATEADD('month', 2, TO_DATE('16'||SUBSTR(period,5), 'DDMonYYYY'))
    END AS payment_deadline,
    dt,
    status,
    CASE
        WHEN status != 'PAID' THEN NULL
        WHEN dt < payment_deadline  THEN 'Y'
        WHEN dt >= payment_deadline THEN 'N'
    END AS on_time_payment
FROM data
;