Since most where statements other than Exists do not keep a load optimized I have created a temp table storing the dates I wish to pull using the Exists(DateKey) to keep my load optimized but I have ran into a problem that makes what I think is an ugly solution when I have multiple tables to load with different Date names.
We already have a calendar table and the where allows me to only grab the dates I want. I have named the date according to the Payments table I will later load.
DateKey as PostedDt
where DateKey >= YearStart(today(), -1) and DateKey <= MonthEnd(today());
FROM Payments.qvd (qvd)
This works great and is optimized. The problem is I am then going to add a second table where the Date field is name CreateDt and this load will not be optimized because Exists(PostedDt, CreateDt) seems to unOptimize it.
From Journals.qvd (qvd)
where Exists(PostedDt, CreateDt);
The only solution I could think of was to do a Resident load of my tDate table and rename the Date to suit each table I am about to load. It would be fast but kind of ugly in my opinion.