In my system i have customers and Sales orders.
Customers are classified with a customer hierarchy and have validity dates: Valid from / valid to
So i would like to return the sales orders according to the active customer hierarchy at the sales order creation date.
Hierarchy A -> Customer 1 : valid from 20130101 / valid to 20131231
Hierarchy B -> Customer 1: valid from 20140101 / valid to 20141231
Sales orders created for Customer 1 have dates from 20130101 to 20140729
I would like to have as a result:
Hierarchy A / Customer 1 / Sales orders with creation date between 20130101 and 20131231
Hierarchy B / Customer 1 / Sales orders with creation date between 20140101 and 20141231
I have a table "Customers" with validity dates and a "Sales orders" table with creation dates and customer codes.
How can i do that ?