There are four tables
First - Customer Second - Invoice Third - Supplier Fourth - Supplier_Remit
Tables details are mentioned below
| Customer_id | Customer_Account_number | Customer_Status | Supplier_id | Supplier_Remit_id |
|---|---|---|---|---|
| 1 | 1501 | Active | 11 | 111 |
| 2 | 1502 | Inactive | 12 | 112 |
| 3 | 1503 | Active | 13 | 113 |
| 4 | 1504 | Active | 14 | 114 |
| 5 | 1505 | Inactive | 15 | 115 |
| Invoice_Date | Invoice_Amount | Invoice_Number | Payment Method | Customer_id |
|---|---|---|---|---|
| 01/01/2023 | 100 | 1000001 | Cash | 1 |
| 12/01/2022 | 150 | 1000002 | Credit Card | 1 |
| 11/09/2022 | 200 | 1000003 | Credit Card | 1 |
| 12/09/2022 | 300 | 1000004 | Cash | 2 |
| 04/15/2022 | 1000 | 1000005 | Cash | 2 |
| 04/15/2022 | 1000 | 1000006 | Credit Card | 3 |
| 10/31/2022 | 250 | 1000007 | Cash | 4 |
| 10/25/2022 | 250 | 1000008 | Cash | 4 |
| 09/20/2022 | 130 | 1000009 | Credit Card | 5 |
| 05/20/2022 | 120 | 10000010 | Credit Card | 5 |
| Supplier_Name | Supplier_id |
|---|---|
| ABC | 11 |
| ACCC | 12 |
| ADEF | 13 |
| AJKL | 14 |
| AFLR | 15 |
| City | Country | Supplier_Remit_id | Supplier_id |
|---|---|---|---|
| Boston | US | 111 | 11 |
| Oak | US | 112 | 12 |
| Albany | US | 113 | 13 |
| Madison | US | 114 | 14 |
| Los Ang | US | 115 | 15 |
I need help in finding the most recent payment method, most recent invoice amount, no of count of invoices missing for current year (2023) and no of count of invoices missing for previous year(2022)
I have written query to find first few columns but unable to write further to get the above mentioned details
select c.customer_id,c.customer_account_number,c.customer_status,sr.country,max(i.invoice_date) as Latest receieved_Invoice_date
from
customer c,
invoice i,
supplier s,
supplier_Remit sr
where
c.customer_status='Active' and
sr.supplier_id=s.supplier_id and
c.supplier_remit_id=sr.supplier_remit_id and
c.customer_id=i.customer_id
group by
c.customer_id,c.customer_account_number,c.customer_status,sr.country;
My expected output would be as below
| Customer_id | Cust_Acct_Num | Cust_Status | Country | Last_Inv_Rec_Date |
|---|---|---|---|---|
| 1 | 1501 | Active | US | 01/01/2023 |
| 3 | 1503 | Active | US | 04/15/2022 |
| 4 | 1504 | Active | US | 10/31/2022 |
| Latest_Paym_Method | Lastest_Inv_Amt | Count of Missing Inv for Curr Yr |
|---|---|---|
| Cash | 100 | 0 |
| Credit card | 1000 | 1 |
| Cash | 250 | 1 |
| Count of Missing Invoices for Prev Year |
|---|
| 10 |
| 11 |
| 11 |
CodePudding user response：
You can use MAX(...) KEEP (DENSE_RANK LAST ORDER BY invoice_date) to get values for the latest invoice and conditional aggregation to count the number of months where there are invoices and then subtract from the total number of months to find the missing invoices:
SELECT c.Customer_id,
c.Customer_Account_number,
c.Customer_Status,
r.country,
i.last_invoice_date,
i.latest_payment_method,
i.latest_invoice_amount,
EXTRACT(MONTH FROM SYSDATE) - COALESCE(i.missing_invoices_this_year, 0)
AS missing_invoices_this_year,
12 - COALESCE(i.missing_invoices_last_year, 0)
AS missing_invoices_last_year
FROM customer c
INNER JOIN supplier_remit r
ON (c.supplier_id = r.supplier_id)
LEFT OUTER JOIN (
SELECT customer_id,
MAX(invoice_date) AS last_invoice_date,
MAX(payment_method) KEEP (DENSE_RANK LAST ORDER BY invoice_date)
AS latest_payment_method,
MAX(invoice_amount) KEEP (DENSE_RANK LAST ORDER BY invoice_date)
AS latest_invoice_amount,
COUNT(
DISTINCT
CASE
WHEN invoice_date < SYSDATE
AND invoice_date >= TRUNC(SYSDATE, 'YY')
THEN TRUNC(invoice_date, 'MM')
END
) AS missing_invoices_this_year,
COUNT(
DISTINCT
CASE
WHEN invoice_date < TRUNC(SYSDATE, 'YY')
AND invoice_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YY'), -12)
THEN TRUNC(invoice_date, 'MM')
END
) AS missing_invoices_last_year
FROM invoice
GROUP BY customer_id
) i
ON (c.customer_id = i.customer_id)
WHERE c.customer_status = 'Active';
Which, for the sample data:
CREATE TABLE customer (Customer_id, Customer_Account_number, Customer_Status, Supplier_id, Supplier_Remit_id) AS
SELECT 1, 1501, 'Active', 11, 111 FROM DUAL UNION ALL
SELECT 2, 1502, 'Inactive', 12, 112 FROM DUAL UNION ALL
SELECT 3, 1503, 'Active', 13, 113 FROM DUAL UNION ALL
SELECT 4, 1504, 'Active', 14, 114 FROM DUAL UNION ALL
SELECT 5, 1505, 'Inactive', 15, 115 FROM DUAL;
CREATE TABLE invoice (Invoice_Date, Invoice_Amount, Invoice_Number, Payment_Method, Customer_id) AS
SELECT DATE '2023-01-01', 100, 1000001, 'Cash', 1 FROM DUAL UNION ALL
SELECT DATE '2022-12-01', 150, 1000002, 'Credit Card', 1 FROM DUAL UNION ALL
SELECT DATE '2022-11-09', 200, 1000003, 'Credit Card', 1 FROM DUAL UNION ALL
SELECT DATE '2022-12-09', 300, 1000004, 'Cash', 2 FROM DUAL UNION ALL
SELECT DATE '2022-04-15', 1000, 1000005, 'Cash', 2 FROM DUAL UNION ALL
SELECT DATE '2022-04-15', 1000, 1000006, 'Credit Card', 3 FROM DUAL UNION ALL
SELECT DATE '2022-10-31', 250, 1000007, 'Cash', 4 FROM DUAL UNION ALL
SELECT DATE '2022-10-25', 250, 1000008, 'Cash', 4 FROM DUAL UNION ALL
SELECT DATE '2022-09-20', 130, 1000009, 'Credit Card', 5 FROM DUAL UNION ALL
SELECT DATE '2022-05-20', 120, 10000010, 'Credit Card', 5 FROM DUAL;
CREATE TABLE supplier (Supplier_Name, Supplier_id) AS
SELECT 'ABC', 11 FROM DUAL UNION ALL
SELECT 'ACCC', 12 FROM DUAL UNION ALL
SELECT 'ADEF', 13 FROM DUAL UNION ALL
SELECT 'AJKL', 14 FROM DUAL UNION ALL
SELECT 'AFLR', 15 FROM DUAL;
CREATE TABLE supplier_remit (City, Country, Supplier_Remit_id, Supplier_id) AS
SELECT 'Boston', 'US', 111, 11 FROM DUAL UNION ALL
SELECT 'Oak', 'US', 112, 12 FROM DUAL UNION ALL
SELECT 'Albany', 'US', 113, 13 FROM DUAL UNION ALL
SELECT 'Madison', 'US', 114, 14 FROM DUAL UNION ALL
SELECT 'Los Ang', 'US', 115, 15 FROM DUAL;
Outputs:
| CUSTOMER_ID | CUSTOMER_ACCOUNT_NUMBER | CUSTOMER_STATUS | COUNTRY | LAST_INVOICE_DATE | LATEST_PAYMENT_METHOD | LATEST_INVOICE_AMOUNT | MISSING_INVOICES_THIS_YEAR | MISSING_INVOICES_LAST_YEAR |
|---|---|---|---|---|---|---|---|---|
| 1 | 1501 | Active | US | 2023-01-01 00:00:00 | Cash | 100 | 0 | 10 |
| 3 | 1503 | Active | US | 2022-04-15 00:00:00 | Credit Card | 1000 | 1 | 11 |
| 4 | 1504 | Active | US | 2022-10-31 00:00:00 | Cash | 250 | 1 | 11 |
CodePudding user response：
In order to find what's missing, you have to first define what should be there, so you need to create a calendar of every month. Then you can use outer joins to the invoice table to find where there aren't any records for that month for that customer. There are lots of ways to write SQL to do this. Here's one:
WITH months AS(SELECT /* MATERIALIZE */ *
FROM (SELECT 'Current' year,
ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),ROWNUM-1) month_start
FROM [any table with at least 12 rows]
WHERE ROWNUM <= 12)
WHERE month_start < SYSDATE
UNION ALL
SELECT 'Previous' year,
ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-12),'YYYY'),ROWNUM-1)
FROM [any table with at least 12 rows]
WHERE ROWNUM <= 12)
SELECT customer.*,
inv.invoice_amount most_recent_invoice_amount,
inv.payment_method most_recent_payment_method,
(SELECT COUNT(*)
FROM months,
invoice
WHERE months.year = 'Current'
AND months.month_start = TRUNC(invoice_date( ),'MM')
AND invoice.customer_id( ) = customer.customer_id
AND invoice.customer_id IS NULL) missed_current_year_months,
(SELECT COUNT(*)
FROM months,
invoice
WHERE months.year = 'Previous'
AND months.month_start = TRUNC(invoice_date( ),'MM')
AND invoice.customer_id( ) = customer.customer_id
AND invoice.customer_id IS NULL) missed_previous_year_months
FROM customer
OUTER APPLY (SELECT invoice_amount,
payment_method
FROM (SELECT invoice_amount,
payment_method,
ROW_NUMBER() OVER (ORDER BY invoice_date DESC) seq
FROM invoice
WHERE invoice.customer_id = customer.customer_id)
WHERE seq = 1) inv