I have a report I have to create on how many calls we recieved for each promotion. We have a Promotion table that stores the Promotion Name, Promotion Start Date, and Phone Number. (No End Date)
We need to link this to the 'Call Log' table that only has the Date of the Call, and the Phone Number.
Normally, we could join on the phone number as that is unique for the Promotion when the promotion is active and get the count of how many calls were made. However, once the promotion is over the phone number will be used for another Promotion and this phone number could be used multiple times in a year.
Without an End Date in the Promotion table, I can't do a join using a date range between start and end date to get the number of calls from the 'Call Log' table and I am looking to see if anyone can tell me if there is a way to determine the End date of the promotion. The key of the Promotion table is the Start Date and Phone number for each Promotion. What I need to do is use the Start date of the next promotion that uses that phone number as the End date for the previous promotion. The problem I can't overcome is if this phone number is used multiple times, how do I get the end date?
Hope this is clear...