I have a table called customers and payments. The payment table tells me the date a customer has paid. I expect every customer to do at least 1 payment every financial year (1st July to 30th June). I want to find the customers and the financial years that they missed payments since 2019 Financial year to the present financial year. If they attempted to pay (where amount is 0), I count that as paid.

A financial year for 2019 would be 1st July 2018 inclusive to 30th June 2019 inclusive.

There can be 3 cases:

• Customer paid every single financial year
• Customer paid in 1 or more years but missed 1 or more years
• Customer didn't pay at all

Table

    CREATE TABLE customers (
 name varchar2(32) not null 
);

CREATE TABLE payments (
  cus_name varchar2(32) not null, 
  date_paid date not null,
  amount_paid number not null
);

Dummy Data

    INSERT INTO customers (name) VALUES ('Bob');
    INSERT INTO customers (name) VALUES ('Sarah');
    INSERT INTO customers (name) VALUES ('James');
    INSERT INTO customers (name) VALUES ('Andrew');
    
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2018-02-12', 'yyyy-mm-dd'), 84);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2019-05-23', 'yyyy-mm-dd'), 54);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2019-05-27', 'yyyy-mm-dd'), 9);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2020-06-14', 'yyyy-mm-dd'), 87);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2021-02-12', 'yyyy-mm-dd'), 84);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2022-04-21', 'yyyy-mm-dd'), 43);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Bob', TO_DATE('2022-08-03', 'yyyy-mm-dd'), 34);

INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Sarah', TO_DATE('2020-08-17', 'yyyy-mm-dd'), 34);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('Sarah', TO_DATE('2021-09-11', 'yyyy-mm-dd'), 0);


INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('James', TO_DATE('2019-12-01', 'yyyy-mm-dd'), 65);
INSERT INTO payments (cus_name, date_paid, amount_paid) VALUES ('James', TO_DATE('2020-07-01', 'yyyy-mm-dd'), 43);

Result I want:

When I tried this, I get confused because I believe I have to do an full outer joint to get my result, but I am not sure what to full outer joint to. Or maybe I am not thinking correctly and I don't need a full outer join.

SELECT
c.name as Customer_Name,
--Year_They_Didnt_Pay
FROM customers c
OUTER JOIN payments p
ON c.name = p.cus_name

Fiddle: http://sqlfiddle.com/#!4/197d50

CodePudding user response：

You can generate a calendar for all the years and then CROSS JOIN it to the names and use NOT EXISTS with the payments table to find the missing years:

WITH calendar (year) AS (
  SELECT ADD_MONTHS(DATE '2018-07-01', 12*(LEVEL-1))
  FROM   DUAL
  CONNECT BY ADD_MONTHS(DATE '2018-07-01', 12*(LEVEL-1)) <= SYSDATE
)
SELECT c.name,
       EXTRACT(YEAR FROM l.year)   1 AS year_not_paid
FROM   calendar l
       CROSS JOIN customers c
WHERE  NOT EXISTS (
         SELECT 1
         FROM   payments p
         WHERE  c.name = p.cus_name
         AND    l.year <= p.date_paid
         AND    p.date_paid < ADD_MONTHS(l.year, 12)
       )

Which, for the sample data, outputs:

NAME	YEAR_NOT_PAID
James	2023
James	2022
James	2019
Andrew	2023
Andrew	2022
Andrew	2021
Andrew	2020
Andrew	2019
Sarah	2023
Sarah	2020
Sarah	2019

db<>fiddle here