Click here to Skip to main content
15,066,193 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to subtract values from two sql datatables?I have two datatable where I want to first match table1 "partnum" columns and if its match with table 2 "partnum" then subtract table1 "FinalstockIN"values from table2"FinalStockout" then display it in another column.Also, how to stop the subtraction if the minus value is neagtive then show 0 or null or dont show at all.

What I have tried:

SQL
with spare_parts as (
 
 SELECT s.SN,partnum,partdesc,partmodel,stockin,dateofstockin, (SELECT 
   SUM(stockin)
   FROM Tbl_SpareParts
   GROUP BY partnum
   Having partnum = s.partnum) AS FinalTotalStockIN FROM Tbl_SpareParts s

),
cases as (
 SELECT b.SN,partnum,partdesc,partmodel,outstock,outstockdate,outstockcaseid_billnum,remarks, (SELECT SUM(outstock)
   FROM Tbl_OutCaseID
   GROUP BY partnum
   having partnum = b.partnum) AS FinalTotalStockOut FROM Tbl_OutCaseID b
)
select
 s.SN,s.partnum,s.partdesc, s.partmodel, s.stockin, 
  s.dateofstockin, s.FinalTotalStockIN,
  c.outstock, 
  c.outstockdate, c.outstockcaseid_billnum, 
  c.remarks, c.FinalTotalStockOut,(FinalTotalStockIN - FinalTotalStockOut) as balance
  from
 
  spare_parts s
  join cases c on s.partnum = c.partnum
Posted
Updated 26-Nov-17 22:58pm
v3
Comments
Thomas Nielsen - getCore 23-Nov-17 9:52am
   
i'd recommend getting the data from the database and do your logics in c#, it's much easier than using tsql for that. You'll have to make a rather complex query if doing what you will, could that work for you?

how about this?

SQL
select 
IF ISNULL(Table2.FinalStockout-Table1.FinalstockIN) then 0 else (Table2.FinalStockout-Table1.FinalstockIN)
 as FinalstockIN
from Table1 inner join Table2 on Table1.partnum=Table2.partnum
   
;WITH CTE AS (
SELECT DISTINCT 
        TSP.SN,TSP.partnum,TSP.partdesc,TSP.partmodel,TSP.stockin,TSP.dateofstockin,
        SUM(TSP.stockin)OVER(PARTITION BY TSP.partnum ORDER BY (SELECT 1))AS 
        FinalTotalStockIN,
        TOC.outstock,TOC.outstockdate, TOC.outstockcaseid_billnum, TOC.remarks,
        SUM(TOC.outstock)OVER(PARTITION BY TOC.partnum ORDER BY (SELECT 1)) AS 
        FinalTotalStockOut
  FROM
   DBName1.dbo.Tbl_SpareParts AS TSP 
     INNER JOIN DBName2.dbo.Tbl_OutCaseID TOC 
      ON (TSP.Partnum = TOC.Partnum)
              )

SELECT  SN,Partnum,PartModel,StockIn,DateofStockIn,FinalTotalStockIN,Outstock,
        OutStockDate,OutStockCaseid_billnum,Remarks,FinalTotalStockOut,
        CASE 
         WHEN SIGN((FinalTotalStockIN - FinalTotalStockOut))=1 
          THEN  
           (FinalTotalStockIN - FinalTotalStockOut)
             ELSE
           0 END as Balance

 FROM CTE
   

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