TheTallBloke
New Member
- Joined
- Sep 3, 2015
- Messages
- 30
Hi all,
My company are running a very primitive promotion 'calendar' where products would get assigned to one or more promotions over a calendar year, and I have been tasked with 'tagging' whether sales at any given time were made as part of the promo, or outside the promo.
I am struggling to understand how to efficiently build a correlation relationship between the following tables in the data model (dummy data), where only the values in red below would qualify as 'sold whilst an active promotion was running'.
I am also struggling to create 2 active relations between Start and End to the Calendar table in my data model, is there a way to avoid having to split the promotion table into two sub-tables and mapping them independently?
Many thanks in advance for the help!
My company are running a very primitive promotion 'calendar' where products would get assigned to one or more promotions over a calendar year, and I have been tasked with 'tagging' whether sales at any given time were made as part of the promo, or outside the promo.
I am struggling to understand how to efficiently build a correlation relationship between the following tables in the data model (dummy data), where only the values in red below would qualify as 'sold whilst an active promotion was running'.
I am also struggling to create 2 active relations between Start and End to the Calendar table in my data model, is there a way to avoid having to split the promotion table into two sub-tables and mapping them independently?
| Sales Date | Product code | Quantity | Total |
31/12/2021 | A | 1 | £5 |
01/01/2022 | A | 1 | £5 |
01/02/2022 | A | 1 | £5 |
01/03/2022 | B | 1 | £5 |
01/04/2022 | B | 1 | £5 |
01/05/2022 | B | 1 | £5 |
01/06/2022 | C | 1 | £5 |
01/07/2022 | C | 1 | £5 |
01/08/2022 | C | 1 | £5 |
01/09/2022 | C | 1 | £5 |
01/10/2022 | C | 1 | £5 |
01/11/2022 | D | 1 | £5 |
01/12/2022 | E | 1 | £5 |
| Product | Promotion code |
| A | 1 |
| B | 1 |
| C | 1 |
| C | 2 |
| C | 3 |
| Promotion code | Start | End |
1 | 01/01/2022 | 31/01/2022 |
2 | 01/03/2022 | 31/03/2022 |
3 | 01/06/2022 | 31/08/2022 |
Many thanks in advance for the help!
