CODEWITHSUNDEEP
sqlpartition-by
Luciano Amaro
Luciano Amaro

Reputation: 51

Return last year value with last_value function in SQL

I need to return the last value of the last date of the year. the problem is that the last value that already comes in SQL is being returned.

Product Date Value Last_Value_Previous_Year
Prod A 31/12/2020 5000.00 2000.00
Prod A 01/01/2020 2000.00 2000.00
Prod A 01/01/2021 1000.00 3000.00
Prod A 01/02/2021 1500.00 3000.00
Prod A 01/03/2021 1000.00 3000.00
Prod A 01/04/2021 3000.00 3000.00

I need:

Product Date Value Last_Value_Previous_Year
Prod A 31/12/2020 5000.00 5000.00
Prod A 01/01/2020 2000.00 5000.00
Prod A 01/01/2021 1000.00 3000.00
Prod A 01/02/2021 1500.00 3000.00
Prod A 01/03/2021 1000.00 3000.00
Prod A 01/04/2021 3000.00 3000.00

For the year 2020 it is returning the value of the last date that appears, when it should be the last of the year. I already tried to use the query

select 
    last_value(value) over (partition by Product order by to_char(date, 'YYYY')) 
from table

Upvotes: 0

Views: 569

Answers (2)

Florin
Florin

Reputation: 555

This query will work in Oracle.

SELECT          PRODUCT, 
                DATA, 
                VALUE, 
                LAST_VALUE(VALUE)
OVER (
  PARTITION BY  EXTRACT(YEAR FROM TO_DATE(DATA,'DD-MM-YYYY')) 
  ORDER BY      EXTRACT(YEAR FROM TO_DATE(DATA,'DD-MM-YYYY')) 
)               AS LAST_VALUE
FROM            your_table

Upvotes: 1

Christophe
Christophe

Reputation: 696

I would add to_char(date,'YYYY') in the partition by statement to group by product + year and ensure you'll have the latest value of the year, then order by date instead of year (actually, ordering by year will not really sort data).

Thus, you could try this :

select Product, Date, last_value(value) over (partition by Product, to_char(date,'YYYY') order by date)
  from table

Upvotes: 1

Related Questions