I would like to find the Date a Customer placed their First Order from a specific Contract. I believe (not 100% sure) the best method is to create a new table by joining the customer table and the contracts table.
I would like advice on how to create the new table as well as how to create the Contract FOD(first order date) field.
Customer Table
Contract Table
Sales table
I would like to create a new table by joining the customer table and the contracts table.
Customer Table
CutomerID Customer GroupID
-Customer A -Group A
-Customer B -Group B
-Customer C -Group A
Contract Table
ContractID Contract GroupID
-ContractA -Group A
-ContractB -Group A
-ContractC -Group A
-ContractD -Group B
-ContractE -Group B
I would like to create a new table by JOIN (ing) the Contracts table and the Customer table where the Contract Group ID = Customer Group ID....
NEW Table
CustomerID ContractID GroupID Contract FOD(First Order Date)
-Customer A -ContractA -Group A 05/05/2017 (date Cust A first order from Contract A)
-Customer A -ContractB -Group A 08/05/2016 (date Cust A first order from Contract B)
-Customer A -ContractC -Group A
-Customer C -ContractA -Group A
-Customer C -ContractB -Group A
-Customer C -ContractC -Group A
-Customer B -ContractD -Group B
-Customer B -ContractE -Group B
I would like advice on how to create the new table as well as how to create the Contract FOD(first order date) field.
The Sales table includes the following fields --- CustomerID, ContractID, GroupID
Thanks for any advice
