How to subtract rows of similar columns and values from two datatables?

Question

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 23-Nov-17 2:42am

Fademay Darkness

Updated 26-Nov-17 22:58pm

Jörgen Andersson

v3

Add a Solution

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?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RDBurmon · Answer 1 · 2017-11-23T23:40:00

Solution 1

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

Posted 23-Nov-17 23:40pm

RDBurmon

Santosh kumar Pithani · Answer 2 · 2017-11-26T18:04:00

;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