Hi @Analyst_SQL ,
Please also refer below:
;with cte as (
select a.Contno,a.ConWeight,a.E_Date,SUm(c.R_Weight) R_Weight,a.cont_value
,Sum(a.cont_value*c.R_Weight) [Value]
FROM #Containerno a
INNER JOIN #tbl_ContD b ON a.CID = b.CID
INNER JOIN #tbl_Issuance_Rags c ON b.D_ID = c.D_ID
group by a.Contno,a.ConWeight,a.E_Date,a.cont_value)
,cte1 as (
select sum(R_Weight) SUMR_Weight,sum([Value]) [SumValue]
from cte)
select a.Contno,a.ConWeight,a.E_Date,a.R_Weight,a.cont_value,a.Value
,cast((cast(a.R_Weight as float)/cast(b.SUMR_Weight as float)) as decimal(7,5)) Perc
from cte a,cte1 b
union
select null,null,null,SUMR_Weight,null,[SumValue],1
from cte1
order by R_Weight
Output:
Contno ConWeight E_Date R_Weight cont_value Value Perc
A02120 100 2021-01-04 65 0.2040 13.2600 0.43333
B1512 300 2021-02-15 85 1.5425 131.1125 0.56667
NULL NULL NULL 150 NULL 144.3725 1.00000
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.