Click here to Skip to main content
15,120,268 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i have 2 tables purchase and sale and both has field quantity and itemname, i want to sum quantity field of both the table and subtract them to find stock where itemname='xyz'.

My query is

SQL
SELECT as2.itemname AS [ITEM NAME], 
SUM(as2.quantity) / 2 AS PURCHASED,
SUM(AS1.quantity) / 2 AS SOLD, 
SUM(as2.quantity) / 2 - SUM(AS1.quantity) / 2 AS Expr3 
FROM purchase AS as2 
RIGHT OUTER JOIN sale AS AS1 ON AS1.itemname = as2.itemname 
GROUP BY AS1.itemname, as2.itemname
Posted
Updated 28-Oct-13 22:19pm
v2
Comments
Ranjan.D 28-Oct-13 10:48am
   
Just join tables and apply logic.
Pranav_ 28-Oct-13 10:57am
   
SELECT as2.itemname AS [ITEM NAME], SUM(as2.quantity) / 2 AS PURCHASED, SUM(AS1.quantity) / 2 AS SOLD, SUM(as2.quantity) / 2 - SUM(AS1.quantity) / 2 AS Expr3 FROM purchase AS as2 RIGHT OUTER JOIN sale AS AS1 ON AS1.itemname = as2.itemname GROUP BY AS1.itemname, as2.itemname /*this is more like a jugad thing i need a proper query, if i remove the /2 it doubles the figure of real one*/
thatraja 28-Oct-13 10:48am
   
your query?
Pranav_ 28-Oct-13 10:51am
   
SELECT as2.itemname AS [ITEM NAME], SUM(as2.quantity) / 2 AS PURCHASED, SUM(AS1.quantity) / 2 AS SOLD, SUM(as2.quantity) / 2 - SUM(AS1.quantity) / 2 AS Expr3
FROM purchase AS as2 RIGHT OUTER JOIN
sale AS AS1 ON AS1.itemname = as2.itemname
GROUP BY AS1.itemname, as2.itemname

/*this is more like a jugad thing i need a proper query, if i remove the /2 it doubles the figure of real one*/

Please check below query
SQL
USE AdventureWorks2008;
GO
SELECT AS1.ProductNumber,AS2.ProductNumber,AS2.SalesOrderQuanity,AS1.PurchaseQuantity,(AS2.SalesOrderQuanity - AS1.PurchaseQuantity) as Stock
FROM
(SELECT PP.ProductNumber,sum(PPOD.OrderQty) as PurchaseQuantity FROM Purchasing.PurchaseOrderDetail PPOD
INNER JOIN Production.Product PP (NOLOCK) ON PP.ProductID = PPOD.ProductID
WHERE PP.ProductNumber = 'BC-M005'
GROUP BY PP.ProductNumber) AS1
INNER JOIN
(SELECT PP.ProductNumber,sum(SSOD.OrderQty) as SalesOrderQuanity FROM Sales.SalesOrderDetail SSOD
INNER JOIN Production.Product PP (NOLOCK) ON PP.ProductID = SSOD.ProductID
WHERE PP.ProductNumber = 'BC-M005'
GROUP BY PP.ProductNumber) AS2 ON AS2.ProductNumber = AS1.ProductNumber
   
Don't do that way. Create another table for Stock & maintain the stock details there.
Update stock data in Stock table while Sales & Insert/Update stock data in Stock Table while Purchase. In this way, there's no need for complex joins & it improves the performance because you have stock details in Stock table. And there're more advantages using this way.
Sample stored procedure for Stock update.
SQL
CREATE PROCEDURE Usp_SALES
(...arguments...)
BEGIN

INSERT INTO SALES_TABLE(...fields...) VALUES(...values...)

--STOCK SHOULD BE DECREASED
UPDATE STOCK_TABLE SET (...fields...)=(...values...)

END

CREATE PROCEDURE Usp_PURCHASE
(...arguments...)
BEGIN

INSERT INTO PURCHASE_TABLE(...fields...) VALUES(...values...)

--STOCK SHOULD BE INCREASED
UPDATE STOCK_TABLE SET (...fields...)=(...values...)

END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900