I have two tables Tbl1 and Tbl2. I have a SQL as follows:
select Tbl1.Col1, Tbl1.Col2, Tbl2.ValA
let outer join
select ColA, ValA, Date1, Date2
on Tbl1.ColA = Tbl2.ColA and
Tbl1.ColDate between Tbl2.Date1 and Tbl2.Date2
There could be a situation where ColA matches but there is no row in Tbl2 where ColDate is between Date1 and Date2. In that case, I have to find the next higher Date1 and Date2 combination in Tbl2 and find the corresponding Tbl2.ValA value for it.
I know that I can write the values into a temporary table first and then update but it seems cumbersome. I cannot use a cursor and traverse through it as the number of rows is huge. Any help would be appreciated.