CODEWITHSUNDEEP
t-sql
jon
jon

Reputation: 3

TSQL Distribute one bucket over several other buckets

I've been pounding my head on this one for two days now.

Here's my issue:

I have 18 buckets. One bucket has a negative value. I need to distribute the bucket with the negative value across the other 17 buckets as a percent of total for the 17 buckets. I can do this in Excel, but I need to do it in T-SQL without any hard coding because this is going to be used in a stored procedure.

Here's my data (Bucket and Amount) and my results from Excel (Pct of Total, Distribution and New Amount):

BUCKET  AMOUNT  [Pct of Total]  Distribution    [New Amount]
1    $174,130.91    9.5384%  $(281.49)   $173,849.41 
2    $54,274.13     2.9730%  $(87.74)    $54,186.39 
3    $150,637.86    8.2515%  $(243.51)   $150,394.34 
4    $389,910.65    21.3581%     $(630.31)   $389,280.34 
5    $379,177.75    20.7702%     $(612.96)   $378,564.79 
6    $79,230.40     4.3400%  $(128.08)   $79,102.32 
7    $47,008.64     2.5750%  $(75.99)    $46,932.64 
8    $47,224.95     2.5868%  $(76.34)    $47,148.60 
9    $102,731.42    5.6273%  $(166.07)   $102,565.35 
10   $8,955.93  0.4906%  $(14.48)    $8,941.45 
11   $43,749.52     2.3965%  $(70.72)    $43,678.80 
12   $16,140.85     0.8841%  $(26.09)    $16,114.76 
13   $72,165.14     3.9530%  $(116.66)   $72,048.48 
14   $23,542.26     1.2896%  $(38.06)    $23,504.21 
15   $874.82    0.0479%  $(1.41)     $873.41 
16   $65,665.10     3.5969%  $(106.15)   $65,558.95 
17   $170,162.38    9.3210%  $(275.08)   $169,887.30 
18   $(2,951.15)            
Total    $1,822,631.55  100.0000%    $(2,951.15)     $1,822,631.55

Upvotes: 0

Views: 94

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Here is an example how you can manage this:

;with neg as(select sum(amount) as amount from t where amount < 0),
      pos as(select * from t where amount >= 0)
select *, 
       p.amount * 100 / sum(p.amount) over() as pct,
       neg.amount * p.amount / sum(p.amount) over() as dist,
       p.amount + neg.amount * p.amount / sum(p.amount) over() as new
from pos p
cross join neg

Fiddle here: http://sqlfiddle.com/#!3/0cdd7/4

Upvotes: 1

Related Questions