Please consider the following data-set.

Can you please advise me the best possible way to get the following output to compare one month with the previous one.

Thank you so much for your help.

CodePudding user response：

you can use the LAG function to do that, example:

create table acounts (
account_no number,
month_end_date DATE, balance number);


insert into acounts values(123960,  '31-Dec-2021',  1000)
insert into acounts values(123960,  '31-Jan-2022',  1500);
insert into acounts values(123960,  '28-Feb-2022',  1700);



sELECT 
    account_no,
    month_end_date, 
    balance,
    LAG(balance) OVER (
        ORDER BY month_end_date
    ) py_sales
FROM 
    acounts
    where account_no = 123960

CodePudding user response：

This quer is for multiple accounts and would summ fi9rst, the balance for every month and year for every acount

WITH CTE AS 
(
sELECT 
    account_no,
    MIN(month_end_date) month_end_date,
    to_char(month_end_date, 'YYYY-MM') monthyear,
    SUM(balance) balance
FROM acounts
GROUP BY     account_no,to_char(month_end_date, 'YYYY-MM')
)
sELECT 
    account_no,
    month_end_date, 
    balance,
    NVL(LAG(balance) OVER (
    PARTITION BY account_no
        ORDER BY monthyear
    ),0) prior_sales
FROM 
    CTE

ACCOUNT_NO | MONTH_END_DATE | BALANCE | PRIOR_SALES
---------: | :------------- | ------: | ----------:
    123960 | 31-DEC-21      |    1000 |           0
    123960 | 31-JAN-22      |    1500 |        1000
    123960 | 28-FEB-22      |    1700 |        1500

db<>fiddle here