I hope this copies ok from excel...
I'm trying to prorate certain records into other record totals to eliminate one of the row types. the source data will only ever have 5 possible Solutions (row types): A, B, C, D, and OTH.
OTH should prorate amongst A, B, and D so it goes away...
Here is the detail table sample.
Detail
FY_CD PD_NO Solution DetailAmount
2018 1 A 50
2018 1 B 75
2018 1 A 41
(hundreds of records with various amounts and solutions from the allowed list)
etc.
A straightforward view with a group by fy/pd/solution gets to the first four columns below.
The bold below is related to the calculation I want to accomplish described below.
This is the results of a view.
FY_CD PD_NO Solution SumAmount |||A+B+D % of A+B+D %c allocated Total
2018 1 A 50,000.00 ||| 0.526315789 5,263.16 55,263.16
2018 1 B 25,000.00 ||| 0.263157895 2,631.58 27,631.58
2018 1 OTH 10,000.00 |||
2018 1 C 37, 000.00 ||| 37,000.00
2018 1 D 20,000.00 ||| 95,000.00 0.210526316 2,105.26 22,105.26
2018 2 ATS 52,000.00 ||| 0.47706422 6,678.90 58,678.90
2018 2 B 27,000.00 ||| 0.247706422 3,467.89 30,467.89
2018 2 OTH 14,000.00 |||
2018 2 C 12,000.00 ||| 12,000.00
2018 2 D 30,000.00 ||| 109,000.00 0.275229358 3,853.21 33,853.21
A, B, and D are supposed to pro-rate OTH among themselves BY PERIOD.
C is left alone.
OTH then becomes zero.
So what I want is
Period 2018/1
A+B+D = 50000 + 25000+ 20000=95000
A/total = 50000/95000=52.6%
C * % = 10000 * 52.6% = 5260 goes to A
A= 50000 + 5620 = 55620
Do the same for B and D. Then OTH is zero as it is spread out. And C just gets to keep being C.
By Period (each period will have its own % for the spread)
So the desired result - can be a second view based on the first.
FY_CD PD_NO Solution SumAmount
2018 1 A 55,263.16
2018 1 B 27,631.58
2018 1 OTH 0 <<< or this record is just missing. That's ok too.
2018 1 C 37,000.00
2018 1 D 22,105.26
2018 2 ATS 58,678.90
2018 2 B 30,467.89
2018 2 OTH
2018 2 C 12,000.00
2018 2 D 33,853.21
I hope I explained it well. I can solve it by hand or maybe with a stored procedure, but I really would prefer a proper set based SQL solution.
I had it formatted better in Excel, but can't figure out how to get it input so it keeps all the formatting.