T-SQL elegant solution to divide a numeric value to multiple accounts

I have a problem that I believe has a perfectly elegant solution, but would like some help.

So I have a table of persons and a numerical value. Besides that, there is a table with the rules of division of that value (per person) to multiple accounts, rule can be either a max value or a percentage of the value.

This is a simplified version of these tables.

Persons(PersonID int, Value decimal)

Account(AccountID int, PersonID int)

Distribution(AccountID int, MaxValue decimal Null, Percentage decimal null)

At some point I need to divide those numerical values to a third table - that holds the account and value divided to that account.

AccountValues(AccountID int, AccountValue decimal)

The count of the accounts (per person) is not fixed. In the distribution table - if both of the distribution values are null - all the left over value goes to that account. The order of distribution is by their ID's.

The data could look something like this.

Persons  table
PersonID    Value
    1            1000,00
    2            2000,00
    3            5000,00
    4            500,00

Accounts table
AccountID   PersonID
1            1
2            1
3            2
4            2
5            2
6            3
7            3
8            4
9            4
10           4

Distribution table
AccountID    MaxValue    Percentage
1            500,00          null
2            null            null
3            null            0,5
4            null            0,2
5            null            null
6            1000,00         null
7            null            null
8            2000,00         null
9            null            0,2
10           null            null

Still a bit new to T-SQL so need help with the simplest and most efficient solution.

So for now I'm thinking of 3 possible solutions. 1. The least elegant - count the max number of accounts per person and do a loop that many times. 2. Cursors - the best way perhaps? 3. CTE recursion (about which I know nothing about)

Answers


I've used a CTE. There might be a smarter way to do the totalling, but I think this works.

Data setup:

declare @Persons table (PersonID int not null,Value decimal(18,4) not null)
insert into @Persons(PersonID,Value) values
(1,1000.00),
(2,2000.00),
(3,5000.00),
(4,500.00)

declare @Accounts table (AccountID int not null,PersonID int not null)
insert into @Accounts(AccountID,PersonID) values
(1,1),
(2,1),
(3,2),
(4,2),
(5,2),
(6,3),
(7,3),
(8,4),
(9,4),
(10,4)

declare @Distribution table (AccountID int not null,MaxValue decimal(18,4) null,Percentage decimal(6,5) null)
insert into @Distribution (AccountID,MaxValue,Percentage) values
(1,500.00,null),
(2,null,null),
(3,null,0.5),
(4,null,0.2),
(5,null,null),
(6,1000.00,null),
(7,null,null),
(8,2000.00,null),
(9,null,0.2),
(10,null,null)

declare @AccountValues table (AccountID int not null,Value decimal(18,4) null)

Actual query:

;With DisbValues as (
    select
        a.AccountID,
        p.PersonID,
        CASE
            WHEN d.MaxValue is not null then d.MaxValue
            WHEN d.Percentage is not null then d.Percentage * p.Value
        END as Value,
        p.Value as TotalAvailable
    from
        @Distribution d
            inner join
        @Accounts a
            on
                d.AccountID = a.AccountID
            inner join
        @Persons p
            on
                a.PersonID = p.PersonID
), CumulativeValues as (
    select
        AccountID,
        PersonID,
        Value,
        COALESCE((select SUM(Value) from DisbValues d2 where d2.PersonID = d.PersonID and d2.AccountID < d.AccountID),0) as PrevValue,
        TotalAvailable
    from
        DisbValues d
)
insert into @AccountValues (AccountID,Value)
select
    AccountID,
    CASE WHEN PrevValue < TotalAvailable THEN
        CASE WHEN PrevValue + Value < TotalAvailable THEN Value --Entirely satisfied
        ELSE TotalAvailable - PrevValue --Partially satisfied
        END
    ELSE
        0 --Not satisfied
    END
from CumulativeValues

The first CTE (DisbValues) eliminates the need to think in terms of percentages (I've assumed that we're working with a percentage of the total value available, not of the remainder when trying to satisfy a particular account). The second CTE (CumulativeValues) then adds up all of the values that earlier accounts would require to be filled.

We can then, in the final query, break things down into 3 cases, as indicated by the comments.


Need Your Help

How do I use HTML/Javascript radio buttons to prompt specific downloads?

javascript html button download radio

I'm trying to let users choose from five radio buttons that link to a separate file, and on the click of a "Accept &amp; Download" button, they download the file that they selected in the radio but...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.