I can't seem to find this simple fact anywhere.
I have a fact table like such in SQL server.
fact_picked Emp_Name Date Apples_Picked John May 1 17 100
And a type 2 dimension table like this
dim_company Emp_Name Company Effective_Since John Blue_Apples June 1 2015 John Apple_N_Stuff Jan 1 2016 John Da_Big_Apple March 17 2017 John Big_Tech October 20 2017
How would I join the fact to the dimension table so I know which "company" picked the 100 apples?
In this case, logically, given the data, it's 'Da_Big_Apple' ... since John began working there in March 17 until Oct 2017, in which his apple picking task took place.
How do I do a join of these tables though? (assuming thousands of records).
I just get stuck. I know I should do something like
Select fp.emp_name, fp.date, fp.apples_picked from fact_picked fp left join dim_company dc on fp.emp_name = dc.emp_name and fp.date > dc.effective_since .... ???
I guess I'm not really sure. I guess I can use a view to convert the type 2 to a type 4 (with an end date in the table). Then it's simpler. Make sure the fact date is greater than the start date, yet before the end date. But is that the most elegant solution?