I need to create a view that given the first two tables (A and B), I get the result like in table C.
Basically I need to fill empty spaces on table B, using the first previous value available like shown below.
I've accomplished this using two loops on a procedure, but I'd like to try a solution using just selects statements.
table_c - Desired result
Does someone has any idea on how to accomplish this?
My sql is very rusty so I have this nagging feeling there's a better way, but what I came up with was to join against a sub-select that's a self join of table_b to make a new table b with date ranges. With that, it's easy to match table_a with the proper value.
I left a test on sqlfiddle so you can see the assumptions I made. This is the code below :
select date_format(a.date,'%m/%d/%Y') as date, b.value as value from table_a as a join (select b1.date as start, IFNULL(min(b2.date),'9999-12-31') as end, b1.value as value from table_b as b1 left outer join table_b as b2 on b1.date < b2.date group by b1.date) as b on a.date >= b.start and a.date < b.end
The self join trims out the extra b2 entries with a group by and taking the min b2 date that's larger than b1's date. In the case of the very last entry, there is no b2 date larger so it ends up null; that I map to 12/31/9999 to be a really large date.