Hi , we run 2014 STD. I have an account status lookup table with changing statuses by row start date. i have a fact table whose create date and account needs to join to the one/first most appropriate "in effect" account lookup row based on fact create date and lookup row start.
You can see the creates, inserts and my lame attempt at doing this in the attached. in the sub select, the where clause is invalid. if i comment that out and instead put the predicate in the on clause, i only get one hit.
what is the correct syntax for this class of problem?
fact create date >= row start date on the first account record whose account is the same as fact account and whose row start dates are sorted descending is what i think i want
i suppose i could attach a row end date to each dim account record using the lag function and storing the results in a temp table would be one way of going about this. then the on condition could be
da1.account=f.account and f.createdate>da1.rowstartdate and f.createdate<=da1.rowendatae...and the join would be straight to the temp, not a sub select. i purposely said > instead of >= so a create date cant match on 2 dim account records. Honestly i'd like to avoid this lag approach.
