dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: Select

  1. #1
    Join Date
    Jan 2013
    Posts
    85

    Select

    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:

    Name:  02.jpg
Views: 12
Size:  48.6 KB

    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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,173

    Re: Select

    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.

  3. #3
    Join Date
    Jan 2013
    Posts
    85

    Re: Select

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

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)