locked
Subtract from two different tables RRS feed

  • Question

  • User1568384800 posted

    Hi All,

    I'm trying to find a way of subtracting a quantity of items issued from a transaction table from a quantity of items delivered.

    I'd like to be able to achieve something like:

    (SELECT        SUM(Quantity) AS QTY
    FROM            dbo.SystemConsumableTransaction
    GROUP BY SystemConsumableItemID)
    
    -
    
    (SELECT        SUM(Quantity) AS DeliveredQty
    FROM            dbo.SystemConsumableDeliveryDetail
    GROUP BY SystemConsumableItemID) AS TotalQty

    There are currently 10 items that have been delivered and so far only 17 issued in transaction so some have never been issued. So if there were 100 delivered and none issued the quantity remaining should be 100.

    Thursday, March 29, 2018 3:59 PM

All replies

  • User347430248 posted

    Hi PurplePrint,

    I suggest you to declare 2 variables and fire 2 queries and store the results in variables. then try to subtract one variable value from other variable.

    Example:

    DECLARE @var1 int;         
       
    SELECT @var1 = (select sum(scd.Quantity) as qty from SystemConsumableDeliveryDetail as scd)
    
    
    
    DECLARE @var2 int;         
       
    SELECT @var2 = (select sum(sct.Quantity) as qty from SystemConsumableTransaction as sct)
    
    SELECT @var2 AS 'Quantity2'; 
    SELECT @var1 AS 'Quantity1';
       
    
    select @var2 - @var1 as 'result';

    Output:

    Regards

    Deepak

    Monday, April 2, 2018 9:53 AM
  • User1568384800 posted

    Need to be able to do it over multiple rows...

    Tuesday, April 3, 2018 9:26 AM
  • User516094431 posted

    Need to be able to do it over multiple rows...

    You can use following approach as per your needs:

    select 
    (select sum(sct.col1) as qty from #tmp1 as sct) - (select sum(sct.col2) as qty from #tmp2 as sct) as 'result1', (select sum(sct.col3) as qty from #tmp1 as sct) - (select sum(sct.col4) as qty from #tmp2 as sct) as 'result1'


      

    Tuesday, April 3, 2018 10:11 AM