CODEWITHSUNDEEP
sqlhana
user11006289
user11006289

Reputation: 1

From a series of invoices with date, how could I aggregate them by invoice month and previous invoice month?

I have a table with invoices of different customers per row. I would like to obtain by using SQL (or SQL HANA) the total amount not only of all of the invoices of the same month and client but also get in another column the total from last month.

Let:

Customer, Invoice Date(YearMonth), Amount
1, 201812, 12
1, 201811, 10
1, 201811, 15
1, 201811, 20
1, 201808, 12
1, 201807, 66
1, 201804, 12
1, 201801, 12    
2, ...
...

=>

Customer, YearMonth Invoices, Total Month, Total Previous Month(YM -1 )
1, 201812, 12, 45
1, 201811, 45, 0 
1, 201808, 12, 66 
1, 201807, 66, 0
1, 201804, 12, 0
1, 201801, 12, 0
2, ..

NB: I also have the exact invoice date and not only the YearMonth

Upvotes: 0

Views: 140

Answers (1)

Eralper
Eralper

Reputation: 6612

Following SQLScript provides the desired solution when executed on HANA database

It includes a subselect statement and use of window functions like SUM with Partition By clause

Additionally, I use date to string and string to date conversion functions and add_months function

select distinct
    Customer,
    InvoiceDate,
    SUM(Amount) OVER (PARTITION BY Customer, InvoiceDate) as TotalCurrent,
    (
        select sum(i.Amount) 
        from Invoices i
        where i.Customer = j.Customer and i.InvoiceDate = TO_CHAR( add_months(TO_DATE(concat(j.InvoiceDate,'01')), -1) ,'YYYYMM')
    ) as PrevTotal
from Invoices j

enter image description here

Upvotes: 0

Related Questions