Fetch last available value if there is NULL

Question

I have a table that is essentially a purchases table that has purchase prices. When a purchase is made, it is recorded at an hour. Like in the table, ABC-123 was purchased on 2022-1-20 at 12:00. I want the NULL values to show 20 as long as a new purchase price is not punched in. Same for the other id_code.

id_code	hour	purchase_price
ABC-123	2022-1-20 12:00	20
ABC-123	2022-1-20 13:00	NULL
ABC-123	2022-1-20 14:00	NULL
BCD-123	2022-1-20 12:00	35
BCD-123	2022-1-20 13:00	36
BCD-123	2022-1-20 14:00	NULL

The output table will look like this: It will replace the NULLs with the previously available price for its particular id_code.

id_code	hour	purchase_price
ABC-123	2022-1-20 12:00	20
ABC-123	2022-1-20 13:00	20
ABC-123	2022-1-20 14:00	20
BCD-123	2022-1-20 12:00	35
BCD-123	2022-1-20 13:00	36
BCD-123	2022-1-20 14:00	36

I did find a similar question here but that seems to not work because my IDs are not incremental integers I think.

You can't do that in SQL. You must correct the `purchase_price` while you're reading the records. — Maurice Perry, Feb 01 '22 at 06:56
have you tried using the example you found and ordering by the hour instead of id? — Julius Tuskenis, Feb 01 '22 at 06:58

score 1 · Answer 1 · answered Feb 01 '22 at 07:13

1

You can create a view with an aggregate function. Try this :

CREATE VIEW test_view AS
( SELECT id_code
       , hour
       , (array_agg(purchase_price) FILTER (WHERE purchase_price IS NOT NULL) OVER (PARTITION BY id_code ORDER BY hour DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING))[1]
    FROM test
)

Result :

id_code hour                array_agg
ABC-123 2022-01-20 12:00:00 20
ABC-123 2022-01-20 13:00:00 20
ABC-123 2022-01-20 14:00:00 20
BCD-123 2022-01-20 12:00:00 35
BCD-123 2022-01-20 13:00:00 36
BCD-123 2022-01-20 14:00:00 36

see the demo in dbfiddle.

answered Feb 01 '22 at 07:13

Edouard

6,577
1
9
20

Hey thanks for the reply. Is there a way to get it done without creating a view? – Maaz Shameem Feb 01 '22 at 09:41
yes you can just execute the query if you don't need the view – Edouard Feb 01 '22 at 10:08

Fetch last available value if there is NULL

1 Answers1