Oracle SQL: Average amount for past few months-CodePudding

I am trying to find the average amount for the recent 4 months for each customer. Below is the sample data

The expected result should be as below. For VENDOR_INVOICE_NUMBER = 3, since the history is for only 2 months. Average should be for those 2 months.

I tried below logic and it is not working as expected. Need some suggesstions.

select vendor_invoice_number, 
       vendor_invoice_number_1 as vendor_invoice_number_date, 
       date,
       amount_due,
       avg(amount_due) over (order by to_date(date, 'dd-mon-yy') range between interval '4' month preceding and current row) as average
from test_915
order by date desc;

CodePudding user response：

It looks like you want average amount only for rows with MAXIMUM date for each customer. May be you should try this:

with invoices as
(
  select vendor_invoice_number, max(date) maxdate
  from test_915
group by vendor_invoice_number
)
 select vendor_invoice_number, 
       vendor_invoice_number_1 as vendor_invoice_number_date, 
       date,
       amount_due,
       case when maxdate is not null then
       avg(amount_due) over (partition by vendor_invoice_number
                      order by to_date(date, 'dd-mon-yy') 
         range between interval '4' month preceding and current row) end as average
from test_915 t
left outer join invoices i
on t.vendor_invoice_number = i.vendor_invoice_number 
           and t.date = i.maxdate
order by date desc

also I'm in doubt with vendor_invoice_number_1 as vendor_invoice_number_date. May be you mean just vendor_invoice_number_date?

CodePudding user response：

You can use ROW_NUMBER() to achieve the result.

See db<>fiddle

WITH cte AS (
SELECT
  vendor_invoice_number, vendor_invoice_number_date, "date", amount_due,
  ROW_NUMBER() OVER(PARTITION BY vendor_invoice_number ORDER BY TO_DATE("date", 'DD-MON-YY') DESC) AS rn
FROM test_915
)

SELECT 
  vendor_invoice_number, vendor_invoice_number_date, "date", amount_due,
  CASE WHEN rn = 1 THEN AVG(CASE WHEN rn <= 4 THEN amount_due END) OVER(PARTITION BY vendor_invoice_number) END AS average
FROM cte