CODEWITHSUNDEEP
sql-server
Crysis90
Crysis90

Reputation: 1

SQL subtraction across two tables when two tables does not match

I have two SQL select queries which return two columns as you can see in the image below. What I want to do is subtract second query's UnsuppliedQty from the first query's relevant stock code's Total column.

Could you guide me the different approaches to do it in here.

SQL Query

Update: Using the query below, I was able to perform the subtraction, however it gives null when the where clause does not match with first table. In this case, I want to return the original value it had.

How do I do it?

select stockcode, totalstock -(
   select UNSUP_QUANT
   from SALESORD_LINES 
   where stockcode='AIRFIL01' 
   and UNSUP_QUANT<>0 
   and dbo.STOCK_ITEMS.STOCKCODE = SALESORD_LINES.STOCKCODE) 
from STOCK_ITEMS

Upvotes: 0

Views: 230

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You may use a simple Join, Like this

;WITH SL
AS
(
    SELECT
        StockCode,
        UNSUP_QUANT = SUM(ISNULL(UNSUP_QUANT,0.00))
        FROM SALESORD_LINES
            WHERE ISNULL(UNSUP_QUANT,0.00) <> 0
            GROUP BY StockCode
)
SELECT
    SL.StockCode,
    TotalStock = SI.TotalStock - SL.UNSUP_QUANT
    FROM STOCK_ITEMS SI
        LEFT JOIN  SL 
            ON SL.StockCode = SI.StockCode

Upvotes: 1

Related Questions