CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformhistorical-db
naomitrina
naomitrina

Reputation: 41

backfill data using previous values in Snowflake

I have a table in Snowflake with data that updates at the end of the month as well as data that updates every day (t2). I've joined these tables and now I need to lag the data points that update monthly until the monthly filingDate is equal to AsOfDate.

This is what my table looks like:

AsOfDate DailyValue ID FilingDate MonthlyValue
7/05/23 158 45 null null
7/04/23 157 45 null null
7/03/23 157 45 null null
7/02/23 154 45 null null
7/01/23 152 45 null null
6/30/23 152 45 6/30/23 4
7/05/23 34 67 null null
7/04/23 33 67 null null
7/03/23 32 67 null null
7/02/23 28 67 null null
7/01/23 28 67 null null
6/30/23 23 67 6/30/23 82

This is what I need my table to look like:

AsOfDate DailyValue ID FilingDate MonthlyValue
7/05/23 158 45 6/30/23 4
7/04/23 157 45 6/30/23 4
7/03/23 157 45 6/30/23 4
7/02/23 154 45 6/30/23 4
7/01/23 152 45 6/30/23 4
6/30/23 152 45 6/30/23 4
7/05/23 34 67 6/30/23 82
7/04/23 33 67 6/30/23 82
7/03/23 32 67 6/30/23 82
7/02/23 28 67 6/30/23 82
7/01/23 28 67 6/30/23 82
6/30/23 23 67 6/30/23 82
6/29/23 22 67 6/27/23 80
6/28/23 21 67 6/27/23 80
6/27/23 20 67 6/27/23 80
6/26/23 19 67 5/31/23 77

I tried using row_number to find the latest filing date for each id.

select row_number over (partition by id order by filingDate desc) as rn

so rn = 1 is the latest filingDate for each date and then I tried using an update statement.

update table 
set MonthlyValue = (select b.MonthlyValue
                    from table b
                    where rn = 1 and b.id = a.id and b.MonthlyValue is not null)
from table a
where a.MonthlyValaue is null

This did not work in Snowflake. I got an error message of unsupported subquery type. I honestly cannot think of a way of doing this without a subquery, however. Any help would be greatly appreciated!!!

Upvotes: 1

Views: 932

Answers (1)

Maja F.
Maja F.

Reputation: 333

This type of question comes up so often that I wrote a blog post about it: Create missing records with Snowflake SQL.

Look at the syntax for using the LAST_VALUE() function.

Upvotes: 0

Related Questions