Prorating totals in a view with group by

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.

I could do a series of joins of subqueries.
select q1.fy, q1.pd, q1.total as 'Atotal', q2.total as 'Btotal', etc.
from
(
sum details for solution A by fy/pd)
as q1
inner join
(sum details for solution B by fy/pd)
as q2
on q1.fy = q2.fy and q1.pd = q2.pd
inner join
(sum details for solution C by fy/pd)
as q3
etc.
Then I'd have a flat row with all the data, but that seems like a lots of sub queries and joins.
and if they add a solution E that is just supposed to flow through like C, then the query has to be changed.. .
Can anyone think of a better way?

Please post you sample data as create table statement and insert statements. Take a look at this post.

hi

i know this topic was from 9 months AGO
very long time .. people forgotten

I tried to do it
I think i have the correct solution

If it helps GREAT
:slight_smile:
:slight_smile:

drop create data ...
use tempdb 

go 


drop table data123
go 

create table data123
(
FY_CD   int NULL,
PD_NO   int NULL,
Solution varchar(100) NULL, 
SumAmount decimal(15,8) NULL
)
go 

insert into data123 select 2018,	1	,'A'	,50000.00 
insert into data123 select 2018,	1	,'B'	,25000.00 
insert into data123 select 2018,	1	,'OTH'	,10000.00 
insert into data123 select 2018,	1	,'C'	,37000.00 
insert into data123 select 2018,	1	,'D'	,20000.00 
insert into data123 select 2018,	2	,'ATS'	,52000.00 
insert into data123 select 2018,	2	,'B'	,27000.00 
insert into data123 select 2018,	2	,'OTH'	,14000.00 	
insert into data123 select 2018,	2	,'C'	,12000.00 
insert into data123 select 2018,	2	,'D'	,30000.00  
go 
 
select * from data123
go
SQL
; WITH abd_sum_cte 
     AS (SELECT pd_no, 
                abd_sum = Sum(CASE solution 
                                WHEN 'A' THEN sumamount 
                                WHEN 'B' THEN sumamount 
                                WHEN 'D' THEN sumamount 
                                ELSE 0 
                              END), 
                oth_sum = Sum(CASE solution 
                                WHEN 'OTH' THEN sumamount 
                                ELSE 0 
                              END) 
         FROM   data123 
         GROUP  BY pd_no) 
SELECT b.*, 
       CASE b.solution 
         WHEN 'A' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
         WHEN 'B' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
         WHEN 'C' THEN b.sumamount 
         WHEN 'OTH' THEN 0 
         WHEN 'D' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
       END 
FROM   abd_sum_cte a 
       JOIN data123 b 
         ON a.pd_no = b.pd_no 

go
Results

image

I posted solution earlier

i forgot to add ATS

here is the SQL

SQL .. New with ATS added
; WITH abd_sum_cte 
     AS (SELECT pd_no, 
                abd_sum = Sum(CASE solution 
                                WHEN 'A' THEN sumamount 
                                WHEN 'B' THEN sumamount 
                                WHEN 'D' THEN sumamount 
                                WHEN 'ATS' THEN sumamount 
                                ELSE 0 
                              END), 
                oth_sum = Sum(CASE solution 
                                WHEN 'OTH' THEN sumamount 
                                ELSE 0 
                              END) 
         FROM   data123 
         GROUP  BY pd_no) 
SELECT b.*, 
       CASE b.solution 
         WHEN 'A' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
         WHEN 'B' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
         WHEN 'C' THEN b.sumamount 
         WHEN 'OTH' THEN 0 
         WHEN 'D' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
         WHEN 'ATS' THEN ( ( b.sumamount / a.abd_sum ) * a.oth_sum ) + b.sumamount 
       END 
FROM   abd_sum_cte a 
       JOIN data123 b 
         ON a.pd_no = b.pd_no