redistribute by total dollar value (not by count)

  • Table X:

    Consumer bigint (PK)

    CurrentGroup bigint

    DollarValue money

    I want to redistribute records in the above table to 4 different groups (currently not in table X) equally by the sum of the DollarValue (not a record count).

    Table X can have up to 5,000 records with a dollar value ranging from $10.00 to $10,000.00

    I know the dollar values will never equal across all of the 4 groups, but it needs to be as close as possible.

    Redistributing by a total count is easy, but I cannot come up with a good strategy based on the SUM of DollarValue.

     

     

  • If the dollar values are randomly distributed across rows then any sample (of sufficient size, about 35 for reasonable precision and confidence) should have an average $/cust that's close to the population mean.  The alternative would be to "jackknife" (take-one-out put-one-in, repeat...) values between the sample sets to get the averages closer to the mean.

    https://en.wikipedia.org/wiki/Jackknife_resampling

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • What is your question?

    And if you're looking for a coded solution, please provide some sample data (as insert statements) and desired results based on that sample data.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply