question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft edited

Display all item from tbl_Bottles.

I want to display all item from tbl_Bottles ,below i tried but not getting expected result.

  Create table #tbl_bottles (B_ID int,B_Name varchar(50))
                                 
        Create table #tbl_GRN (G_ID int,B_ID int,G_Qty int)
        Create table #tbl_Issue (I_ID int,B_ID int,I_Qty int)
    
    
                                    
        insert into #tbl_bottles VALUES(1,'19Liter')
        insert into #tbl_bottles VALUES(2,'6Liter')
        insert into #tbl_bottles VALUES(3,'2Liter')
        insert into #tbl_bottles VALUES(4,'Nozzel')
        insert into #tbl_bottles VALUES(5,'Cap')
               insert into #tbl_bottles VALUES(6,'Stand')
        insert into #tbl_bottles VALUES(7,'Pump')
               insert into #tbl_bottles VALUES(8,'Pip')
        insert into #tbl_bottles VALUES(9,'Blue Stand')
    
               insert into #tbl_GRN VALUES(1,1,5)
        insert into #tbl_GRN VALUES(2,1 ,8)
         insert into #tbl_GRN VALUES(3,3 ,12)
           insert into #tbl_GRN VALUES(4,5 ,6)
                 insert into #tbl_Issue VALUES(1,1,5)
                  insert into #tbl_Issue VALUES(1,1,2)
        insert into #tbl_Issue VALUES(2,3 ,8)
    
    
        ;With Cte1 as (
 Select B_ID as Code,B_Name as Item from #tbl_bottles
 ), cte2
 as
 (
 select B_ID as Code ,Sum(isnull(G_QTY,0)) as GQty   from #tbl_GRN 
 group by B_ID
 )
 ,cte3 as 
 (select B_ID as Code ,Sum(isnull(I_QTY,0)) as IQty   from #tbl_issue
 group by B_ID)
    
 select Item , isnull(GQty-IQty,0)Qty  from Cte1     join cte2 on cte1.Code=cte2.Code 
    
 full   join cte3 on Cte2.Code=cte3.Code


80632-ouput.jpg


sql-server-generalsql-server-transact-sql
ouput.jpg (33.7 KiB)
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.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @akhterhussain-3167,

Please try:

 ;with cte as
 (select tb.B_ID,sum(isnull(tg.G_Qty,0)) G_Qty 
  from #tbl_bottles  tb
  left join #tbl_GRN tg on tb.B_ID=tg.B_ID
  group by tb.B_ID)
 ,cte2 as
 (select tb.B_ID,sum(isnull(ti.I_Qty,0)) I_Qty 
  from #tbl_bottles  tb
  left join #tbl_Issue ti on tb.B_ID=ti.B_ID
  group by tb.B_ID)
 ,cte3 as
 (select t.B_Name Item,c.G_Qty,c2.I_Qty,isnull((c.G_Qty-c2.I_Qty),0) balance 
  from cte c
  join cte2 c2 on c.B_ID=c2.B_ID
  join #tbl_bottles t on c.B_ID=t.B_ID)
    
 select * from cte3
 union all
 select 'Total', sum(G_Qty),sum(I_Qty),sum(balance)
 from  cte3

Output:

     Item G_Qty I_Qty balance
     19Liter 13 7 6
     6Liter 0 0 0
     2Liter 12 8 4
     Nozzel 0 0 0
     Cap 6 0 6
     Stand 0 0 0
     Pump 0 0 0
     Pip 0 0 0
     Blue Stand 0 0 0
     Total 31 15 16

Regards
Echo


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.

nasreen-akter avatar image
1 Vote"
nasreen-akter answered

Hi @akhterhussain-3167,

would you please try the following statement. Thanks!

 select Item , isnull(GQty,0) as GQty, isnull(IQty,0) as IQty, (isnull(GQty,0)-isnull(IQty,0)) as Balance  
 from Cte1    
 left join cte2 on cte1.Code=cte2.Code 
 left join cte3 on Cte2.Code=cte3.Code

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 CTE1 AS (
     SELECT b.B_ID, SUM(g.G_Qty) AS G_Qty
     FROM #tbl_bottles AS b
     LEFT JOIN #tbl_GRN AS g ON g.B_ID = b.B_ID
     GROUP BY b.B_ID
 ),
 CTE2 AS (
     SELECT b.B_ID, SUM(i.I_Qty) AS I_Qty
     FROM #tbl_bottles AS b
     LEFT JOIN #tbl_Issue AS i ON i.B_ID = b.B_ID
     GROUP BY b.B_ID
 ),
 CTE3 AS (
     SELECT 
         'Total' AS Item, 
         G_Qty = (SELECT SUM(G_Qty) FROM #tbl_GRN),
         I_Qty = (SELECT SUM(I_Qty) FROM #tbl_Issue)
 )
    
 SELECT 
     b.B_Name AS Item, 
     ISNULL(c1.G_Qty, 0) AS G_Qty, 
     ISNULL(c2.I_Qty, 0) AS I_Qty,
     ISNULL(c1.G_Qty, 0) - ISNULL(c2.I_Qty, 0) AS Balance
 FROM #tbl_bottles AS b
 LEFT JOIN CTE1 AS c1 ON c1.B_ID = b.B_ID
 LEFT JOIN CTE2 AS c2 ON c2.B_ID = b.B_ID
    
 UNION ALL
    
 SELECT 
     'Total' AS Item, 
     G_Qty,
     I_Qty, 
     G_Qty - I_Qty AS Balance
 FROM CTE3;
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.