Sql query for calculation

Question

I have 4 tables Purchase Purchase Return Sale Sale Return Data on purchase table Data on purchase return table Data on sale table No data in sales return table I want the sum of purchase price based on itemcode having same purchase price from all table where pQty = Qty from purchase table PRQty = Qty from purchase return table and so on the calculation for pQty of Itemcode =1 is 18 (10+8 from purchase table) PRQty of Itemcode =1 is 3 (2+1 from purchase return table) and so on ----------------------------------------------------------------------------------------------------------------------------- The final output The calculation for closing qty of Itemcode 1 = 21 (sum of pQty- sum of PRQty - sum of SQty - sum of SRQty) price of ItemCode 1 = 155 ( (sum of _PPrice- sum of _PRPrice - sum of _SPrice - sum of _SRPrice) same calulation is applied in Itemcode 2 Please help me with the sql query

Shahriar Nikkhah · Answer

Have fun ---************************************************************************ --- Template table ---************************************************************************ DROP TABLE IF EXISTS dbo.tblReport GO ; ---************************************************************************ --- @Purchase ---************************************************************************ DECLARE @Purchase TABLE ( [ItemCode] int , [Purchase Price] int , Qty int ) INSERT INTO @Purchase VALUES (1,5,10) , (2,3,5) , (1,5,8) , (1,10,12) , (2,2,3) , (2,5,3) --SELECT * FROM @Purchase ---************************************************************************ --- @PurchaseReturn ---************************************************************************ DECLARE @PurchaseReturn TABLE ( [ItemCode] int , [Purchase Price] int , Qty int ) INSERT INTO @PurchaseReturn VALUES (1,5,2) , (2,3,3) , (1,5,1) --SELECT * FROM @PurchaseReturn ---************************************************************************ --- @SalesPrice ---************************************************************************ DECLARE @SalesPrice TABLE ( [ItemCode] int , [Purchase Price] int , Qty int , [Sales Price] int ) INSERT INTO @SalesPrice VALUES (1,5,3,10) , (1,5,1,12) , (1,10,2,15) --SELECT * FROM @SalesPrice SELECT p.* , pr.[prQty] , [_prPrice] = p.[Purchase Price] * pr.[prQty] , s.sQty , [_sPrice] = p.[Purchase Price] * s.[sQty] INTO tblReport FROM ( SELECT [ItemCode] , [Purchase Price] , [pQty] = SUM (Qty) , [_pPrice] = SUM (Qty*[Purchase Price]) FROM @Purchase GROUP BY [ItemCode] , [Purchase Price] ) AS P LEFT JOIN ( SELECT [ItemCode] , [_prPrice] = [Purchase Price] , [prQty] = SUM (Qty) FROM @PurchaseReturn GROUP BY [ItemCode] , [Purchase Price] ) AS Pr ON P.[ItemCode] = Pr.[ItemCode] AND P.[Purchase Price] = Pr.[_prPrice] LEFT JOIN ( SELECT [ItemCode] , [_sPrice] = [Purchase Price] , [sQty] = SUM (Qty) FROM @SalesPrice GROUP BY [ItemCode] , [Purchase Price] ) AS s ON P.[ItemCode] = s.[ItemCode] AND P.[Purchase Price] = s.[_sPrice] --SELECT * FROM tblReport SELECT [ItemCode] , [closing qty] = ISNULL ( SUM ([pQty]) , 0) - ISNULL ( SUM ([prQty]) , 0 ) - ISNULL ( SUM ([sQty]) , 0) , [Price] = ISNULL ( SUM ([_pPrice]) , 0) - ISNULL ( SUM ([_prPrice]) , 0 ) - ISNULL ( SUM ([_sPrice]) , 0) FROM tblReport GROUP BY [ItemCode]

Shahriar Nikkhah · Answer

It all depends generally Physical

Devendra Kumar · Answer

Thank you so much sir. I have a small question, which is better to store the data ( using table variable or creating physical table) for better performance?