I have a dimension table with promotion name with their corresponding starting dates.
I have an order table with all order details.
Now I want to find out order total during the first week of every promotion. So for promotion P1, I should have order total for O1 and O2, but not O5 which is placed on 20-01-2019 (after first week of promotion P1, i.e. after 07-01-2019).
Similarly for promotion P2, I should have order total for only O3.