At first, I have a table with NroMaterial as identifier, %Fecha as a date por each row and SSTT as a value that is null in many rows so I want to replace that null values to the average for each NroMaterial that it´s in another table and I want the closest average before the date of the first table.
So, I have a table called Final with:
and other table with the averages called MediasSolidos:
[%Fecha cre], (this must be the closest date to %Fecha but always less than %Fecha)
Media (this is the average for the NroMaterial)
I´ve tried with this code but is not working. Any help will be apreciated.
By the looks of the last left join statement you're going to run into difficulties as you're loading all the columns from Vacios, restricting them by date and then left joining them back to Vacios - this won't work as all the field names are the same.
I'm not entirely sure what you're trying to do so forgive me if any of these suggestions aren't applicable but hopefully they'll point you in the right dicrection.
1. To limit by date simply create a 'Temp_Vacios' table and instead of the final left join simply perform:
Drop Table Temp_Vacios;
2. You may also be getting problems due the max() function in the Where clause, to fix; set the max(Date) in a variable and then call in from the Where statement.
Resident Temp_Vacios Order By Date;
Let v_Max_Date = peek('Date')
Drop Table Date_Temp;
None of that has been checked.
3. You can also get your average during a left join similar to:
Left join (Sales_Data)
sum(Sales) as Total_Sales
Resident Sales_Data Group By Sales_Person;
Again not checked. You can see all the field we're left joining is named differently (Total_Sales) to the one we have in the original Sales_Data field.
My first thought was to do it like your point number 1. But the problem is that Date and Dia are fields from different tables so I can´t to compare them in the where clause beacuse only Date belongs to the resident table.
That´s why I was looking for a distinct solution.
I realized that I was getting problems using max() function so thank you for your solution, I´m going to try that.
I´m trying to do all this with set expression but I think is not the best idea but if someone has an easier solution with set expression...it will be welcome!!