Require Expected Output

Question

Below is dummy Data and expected output

CREATE TABLE #Containerno(CID INT,Contno VARCHAR(50),ConWeight int,E_Date date,cont_value Decimal(10,4));  
CREATE TABLE #tbl_ContD(D_ID INT,CID int);  
Create table #tbl_Issuance_Rags (R_ID int,D_ID int,R_Weight int,R_Date date)  
  
  
INSERT INTO #Containerno VALUES  
   (1,'A02120',100,'2021-01-04','0.2040')  
 , (2,'B1512',300,'2021-02-15','1.5425')  
 , (3,'C2121',100,'2021-03-26','4.0052')  
   
 INSERT INTO #tbl_ContD VALUES   
    (111,1),  
  (112,1),  
   (113,1),  
   (114,2),  
  (115,2),  
   (116,3)  
 INSERT INTO #tbl_Issuance_Rags VALUES   
    (101,111,20,'2021-04-06'),  
  (102,112,30,'2021-04-06'),  
   (103,113,15,'2021-04-06'),  
    (104,114,25,'2021-04-06'),  
     (105,115,25,'2021-04-06'),  
  (106,11,35,'2021-04-06')  

Value=(R_Weight*Cont_Value)
Perc =R_Weight/sum(R_Weight) which is 150

Answer 1

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.

Answer 2

Try this:

;WITH CTE_Sum_Of_R_Weight_per_Contno AS (
    SELECT t1.Contno, t1.ConWeight, SUM(t3.R_Weight) AS Sum_Of_R_Weight_per_Contno
    FROM #Containerno AS t1
    LEFT JOIN #tbl_ContD AS t2 ON t1.CID = t2.CID
    LEFT JOIN #tbl_Issuance_Rags AS t3 ON t2.D_ID = t3.D_ID
    GROUP BY t1.Contno, t1.ConWeight
    HAVING SUM(t3.R_Weight) >= 0
),
CTE_Sum_Of_R_Weight AS (
    SELECT SUM(Sum_Of_R_Weight_per_Contno) AS Sum_Of_R_Weight
    FROM CTE_Sum_Of_R_Weight_per_Contno
),
CTE_Final AS (
    SELECT  c1.Contno, 
            CAST(c1.ConWeight AS varchar(10)) AS ConWeight, 
            CAST(c1.E_Date AS varchar(10)) AS E_Date, 
            c2.Sum_Of_R_Weight_per_Contno AS R_Weight, 
            CAST(c1.Cont_Value AS varchar(10)) AS Cont_Value, 
            c2.Sum_Of_R_Weight_per_Contno * c1.Cont_Value AS Value, 
            c2.Sum_Of_R_Weight_per_Contno * 1.0 / Sum_Of_R_Weight AS Perc,
            2 AS OrderBy
    FROM #Containerno AS c1
    INNER JOIN CTE_Sum_Of_R_Weight_per_Contno AS c2 ON c1.Contno = c2.Contno
    INNER JOIN CTE_Sum_Of_R_Weight ON 1 = 1
)

SELECT Contno, ConWeight, E_Date, R_Weight, Cont_Value, Value, Perc
FROM CTE_Final
UNION
SELECT '' AS Contno, '' AS ConWeight, '' AS E_Date, SUM(R_Weight) AS R_Weight, '' AS Cont_Value, SUM(Value) AS Value, 1 AS Perc
FROM CTE_Final
ORDER BY R_Weight;