# 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  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 I posted solution earlier

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
``````