CODEWITHSUNDEEP
sqlamazon-redshift
Jwan622
Jwan622

Reputation: 11639

How to backfill the nulls with a value from another column 1 row ahead?

I have records that look like this:

enter image description here

I am trying to backfill every null with the value in the effective_date column that is 1 row ahead. So for the first null, the value should be: 2022-09-05 02:32:29.940416. How can I do this?

this is on redshift.

Upvotes: 0

Views: 352

Answers (1)

Mattpats
Mattpats

Reputation: 534

IIF(expiry_date IS NULL, LAG(effective_date, 1) OVER (ORDER BY effective_date DESC), expiry_date)

If effective_date is not consecutive throughout your dataset, then you would need to create an index column.

IIF(expiry_date IS NULL, LAG(effective_date, 1) OVER (ORDER BY idx_col DESC), expiry_date)

Upvotes: 1

Related Questions