I am having trouble tryng to figure out how to model this data.
I have a promotion fact table with a promotion code and a start and an end date.
The data looks like this for example:
Promo start end
1234 2008/12/27 2009/03/21
7896 2009/01/27 2009/07/09
3827 2009/10/27 2009/11/21
I have a normal calendar with a datekey. Now when the user selects a year and month for example 2009 February all promotions that are currently on must be linked so in this case Promo 1234 and 7896.
But obviously the start and end dates do not fall in the date key range of 2009 February, I have thought of a join table with date key and start and end date, but this will cause a loop.
This same issue applies for accounts that have a begin and end date, and I want to know when a date selection is made if and account is "active" in this period.
So in a nutshell how do you model linking facts that have start and end dates, when the start and end date keys do not fall in the date key range of the selected date but the selected date is within the range of start and end date, hope this is clear?
