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)
I've used a CTE. There might be a smarter way to do the totalling, but I think this works.
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)
;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.