Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hey guys. How do I subtract the two columns from different tables base on their product codes?

These are my tables:

CREATE TABLE tblproducts (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
);


CREATE TABLE tblstocks (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_month varchar(100) NOT NULL,
	stocks_year varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL FOREIGN KEY REFERENCES tblproducts(product_barcode)
);


CREATE TABLE tblorder(
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_date varchar(100) NOT NULL,
	product_code varchar(50)
);



I want to subtract the stocks.qty on order_qty. How will I do that? Thanks guys.

What I have tried:

SELECT SUM(ts.stocks_qty) - (SELECT SUM(tbo.order_qty) FROM tblorder AS tbo INNER JOIN tblstocks AS ts ON ts.product_barcode = tbo.product_code) AS Stocks FROM tblstocks AS ts INNER JOIN tblorder AS tbo ON tbo.product_code = ts.product_barcode WHERE ts.product_barcode = tbo.product_code AND ts.stocks_status = 'STOCK IN';
Posted
Updated 11-Mar-18 18:57pm

SELECT (SUM(ts.stocks_qty)-SUM(tbo.order_qty)) AS Stocks
   FROM tblstocks AS ts INNER JOIN tblorder AS tbo
      ON (tbo.product_code = ts.product_barcode)  WHERE ts.stocks_status = 'STOCK IN'
           GROUP BY tbo.product_code ;
 
Share this answer
 
v2
Comments
John Th 12-Mar-18 1:01am    
Hi Santosh. Assume that order_qty has a value 2 for P-0001(product_code) and stocks_qty has a value 35 for P-0001(product_code), so the stocks will be 33. How will I do that? I've tried your query but I got 74 stocks. Btw thank you for respond.
Santosh kumar Pithani 12-Mar-18 1:05am    
You have to do group by ..wait i will improve solution for you.
John Th 12-Mar-18 1:09am    
Alright. I'll wait for it.
John Th 12-Mar-18 1:20am    
Thanks Santosh. Really appreciate your solution.
Santosh kumar Pithani 12-Mar-18 1:22am    
Welcome John:)
Here is an example, hopefully it will fired you up! Assuming the order date is unique.

SQL
DECLARE @tblproducts TABLE  (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
)

INSERT INTO @tblproducts
	SELECT 'ninja1', 'My Ninja product 1', '123', 'abc','GoGO', 100 UNION
	SELECT 'ninja2', 'My Ninja product 2', '123', 'yyy','JO JO', 200 UNION
	SELECT 'ninja3', 'My Ninja product 3', '222', 'xxx','XX JO', 111 

DECLARE @tblorder TABLE (
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_date varchar(100) NOT NULL,
	product_code varchar(50)
)

DECLARE @tblstocks TABLE (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_month varchar(100) NOT NULL,
	stocks_year varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL 
)

INSERT INTO @tblstocks
	SELECT 100,'STOCK IN', '1/1/2018','Jan',2018,'1/1/2099','ninja1' UNION
	SELECT 40,'STOCK IN', '2/1/2018','Jan',2018,'1/1/2099','ninja2' UNION
	SELECT 30,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja1' UNION
	SELECT 50,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja3' UNION
	SELECT 50,'STOCK OUT', '3/1/2018','Feb',2018,'1/1/2099','ninja1' 

INSERT INTO @tblorder
	SELECT 5,'1/3/2018','ninja1' UNION
	SELECT 35,'2/3/2018','ninja1' UNION
	SELECT 15,'2/3/2018','ninja2' UNION
	SELECT 15,'3/3/2018','ninja2' 

;WITH cteSumStocks AS (
	SELECT product_barcode, SUM(stocks_qty) 'TotalStock' FROM @tblstocks WHERE stocks_status='STOCK IN'
		GROUP BY product_barcode
) 
, cteSumOrders AS (
	SELECT product_code, SUM(order_qty) 'TotalOrder' FROM @tblorder
		GROUP BY product_code
) SELECT s.product_barcode, p.product_name, s.TotalStock, o.TotalOrder, s.TotalStock - o.TotalOrder 'AvailableStocks' 
	FROM cteSumStocks s
	JOIN cteSumOrders o
	ON s.product_barcode = o.product_code
	JOIN @tblproducts p
	ON s.product_barcode = p.product_barcode


Output:
barcode	name	                TotalStock	TotalOrder	AvailableStocks
ninja1	My Ninja product 1         130	            40	           90
ninja2	My Ninja product 2	    40	            30	           10
 
Share this answer
 
v2
Comments
John Th 12-Mar-18 1:20am    
Thank you Bryian Tan! It works. Godbless you.
John Th 12-Mar-18 23:59pm    
How about if I want to join the tblproducts to get the product name? What will be the query? I have tried a query below but it doesn't work.

;WITH sumStocks AS (
SELECT ts.product_barcode, tp.product_name, SUM(stocks_qty) 'TotalStock' FROM tblstocks AS ts INNER JOIN tblproducts AS tp ON tp.product_barcode = ts.product_barcode WHERE ts.stocks_status='STOCK IN'
GROUP BY ts.product_barcode, tp.product_name
)
, sumOrders AS (
SELECT product_code, SUM(order_qty) 'TotalOrder' FROM tblorder
GROUP BY product_code
) SELECT s.product_barcode, s.TotalStock, o.TotalOrder, s.TotalStock - o.TotalOrder 'AvailableStocks' FROM sumStocks s
JOIN sumOrders o
ON s.product_barcode = o.product_code;
Bryian Tan 13-Mar-18 0:25am    
see the updated solution.
John Th 13-Mar-18 2:08am    
Thanks Bryian.
Bryian Tan 13-Mar-18 2:12am    
You're welcome. Don't forget to mark this as a solution if it help so we can close this topic.

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