Retrieving Records So that total Records equals Variable Value


  • Hello everyone:

    I am stuck on this area and need a little push. I have a table that holds Customers and Sales Rep.

    I need to set the Max amount of customer that can be assigned to a specific sales rep. So if the max amount of customer is reached, then the customers are assigned to everyone else.

    To start:

    I created and SET a Variable Called: MaxCount as Indicated Below.
    Note: I am only interested in limiting the amount of customer assigned to ELMO.

    SET @MaxCount =   (SELECT   COUNT (*) FROM Schema.CustomerTable WHERE SalesRep = 'Elmo')

    Then I created another Variable called: Threshold

    DECLARE @Threshold INT
    SET @Threshold = 200

    Followed by this statement:

    IF @MaxCount < @Threshold

    Assigned the Customer to SalesRep ELMO so that the total customer assigned is not greater than the Threshold.

    When it reaches Threshold, I will assigned the rest to other sales rep who do not have a threshold.

    This is where I am stuck, I cannot figure out how to stop the assignment so that the total assigned is not more than the Threshold.

    I tried Row number and @@RowCount but does not work.

    I would appreciate any ideas and help.

    Thanks my friends.


    Saturday, September 07, 2013 9:19 PM


All replies