question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft answered

Require Expected Output

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')

85441-image.png

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


sql-server-generalsql-server-transact-sql
image.png (7.8 KiB)
image.png (8.7 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not clear what you want to join here, the second CTE returns only the total sum?
Or do you want to get the total of cte2 for each row of cte1?

0 Votes 0 ·

I want to get sum of whole column(I_Weight) and divided by each row of (I_Weight).

0 Votes 0 ·

Can you explain why R_Weight is 85 for ConWeigh = 300?

0 Votes 0 ·

I have added on more column in image,in which contno is coming,300 is coming from table #Containerno and 85 is sum of #tbl_Issuance_Rags (B1512)

0 Votes 0 ·

In the last row of the sample data (106,11,35,'2021-04-06'), I guess 11 should be 114 or 115.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @akhterhussain-3167

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.