I have searched the community for a solution to my problem, but have not been able to find a solution, so hopefully somebody out there can help.
I have a table tracking what promotions are sent to which members. One member can be sent multiple promtions and one member can therefore be part of more than one promotion at the same time. I am trying to figure out a way in which I can associate the member with one promotion only at any given point in time.
the fields and test data I have at my disposal are:
Member PromotionID StartDate EndDate
1 1 2011/01/01 2011/01/15
1 2 2011/01/10 2011/01/30
1 3 2011/01/20 2011/01/25
1 4 2011/01/29 2011/02/25
1 5 2011/02/10 2011/02/15
1 6 2011/02/10 2011/02/10
2 2 2011/01/10 2011/01/31
2 4 2011/01/29 2011/02/25
A couple of rules that need to be kept in mind:
If 2 promotions have the same start date, we use the one with the highest PromotionID
The ordering of these promotions need to be done per member, and the combination of promotions are unique per member
After the data manipulation, the results need to read as follows:
Member PromotionID StartDate EndDate
1 1 2011/01/01 2011/01/09
1 2 2011/01/10 2011/01/19
1 3 2011/01/20 2011/01/25
1 2 2011/01/26 2011/01/28
1 4 2011/01/29 2011/02/09
1 6 2011/02/10 2011/02/10
1 5 2011/02/11 2011/02/15
2 2 2011/01/10 2011/01/28
2 4 2011/01/29 2011/02/25
The result of the data manipulation is therefor that a member is associated with 1 promotion only at any given point in time, and that it is possibile for the member to be associatiod with the same promotion for multiple time periods if there are time overlaps between promotions
