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
CodePudding user response：
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
CodePudding user response：
Hello this will work-
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
this query is in oracle.