I'm not sure how to accurately phrase this question.
Essentially, say I have a bunch of salespeople on different sales teams. Like, the Blue Region, the Orange Region, the Red Region, whatever.
Now, these sales people are each connected to one sales region, but may, on occasion, change sales regions over time.
Say Bob Jones was in the Blue Region for January 2015, February 2015, March 2015, but on April 2015, he went to the Green Region.
So I'm trying to create a report that would aggregate sales revenue based on Region (even though at the technical level, its logged to sales person).
So we would have transaction (table: employee_sales) data as follows:
name date sales Bob Jones February 18th, 2015 $50,000
So in order to aggregate by region, I would need a helper dimension table as follows (Remember, region can change over time) (table: employee_region)
name region month year Bob Jones Blue January 2015 Bob Jones Blue February 2015 Bob Jones Blue March 2015 Bob Jones Green April 2015
Then I can simply do a query:
select er.region, er.month, er.year, sum(es.sales) from employee_sales es inner join employee_region er on es.name = er.name and month(es.date) = er.month and year(es.date) = er.year group by er.region, er.month, er.year
So this would give me the data I need.
HOWEVER, now I have a problem --- say I have 500 employees and the REGION data is updated a month in arrears. So right now, February 2016, we only have region data from January 2016.
Would I would LIKE to do, is ... for the current month (February 2016) ... if the data in the "helper table" for region is missing for the month, take the last month found (which may be January, but sometimes even December or November potentially).
I'm not sure what to do here. Create some kind of view?
Do I restructure the 'helper table' so it's more of an inequality statement?
EDIT: I think this is a slowly changing dimensions problem. Hmm I probably have to reorganize the dimension table.