Select

[chapran]

I need to create a select statement to get the list of PartialID with corresponding CommissionSplitPercent
There is no direct relation between these values. This is my attempt graphically:



The most left table contains CommissionSplitPercent for the Salesman depending on the date range. For instance the Salesman with ID = 18 has 2 CommissionSplitPercent values, one which is 60% for the dates between 2005/01/01 and 2015/12/31 and another one 20% for dates between 2016/01/01 and 2020/01/01.
My statement gives 2 records for PartialID = 22333. And it should not be like that.
I want to set somehow dependency of the selected CommissionSplitPercent with OurOrderDate value. As you can see in the selected marked with red records OurOrderDate is 2005/04/04, so I would like to get only 1 record for this PartialID with Value 60% because the OurOrderDate is between 2005/01/01 (StartDate) and 2015/12/31 (End Date). If the Salesman is not in the SalesmanCommissionSplitPercent table then 50% should be selected for the PartialID.

Thank you

[Arjay]

I would change the db schema to add a FK relationship to the Partial table and do a one-time query to fix up the data.

From then on it would be a simple select statement.

[chapran]

Sorry I do not understand your advice. Would you please give more details.

Thank you