How to find difference between two fields of two tables

Question

0.00/5 (No votes)

See more:

i have two tables A and B. Both tables have 3 fields. 2 fields in both tables are same as one is 'Article ID' and other is 'Cost'. These 2 fields are common in both tables and data is also common. but the third field in table A is 'qty_should_be' and in table B is 'qty_present'. I want the difference between both fields that how much stock is less or more than it should be

What I have tried:

table A
ART0000024 180.00 100.00
ART0000024 1079.00 1000.00

table B
ART0000024 144 100.00
ART0000024 1099 1000.00

Posted 7-Mar-18 0:11am

Member 13713322

Updated 7-Mar-18 0:28am

Add a Solution

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)

OriginalGriff · Answer 1 · 2018-03-07T00:26:00

Solution 1

Try:

SQL

SELECT a.[Article ID], a.qty_should_be - b.qty_present
FROM TableA a
JOIN TableB b ON a.[Article ID] = b.[Article ID]

Posted 7-Mar-18 0:26am

OriginalGriff

_Asif_ · Answer 2 · 2018-03-07T00:29:00

Try this

DECLARE @A TABLE
(
ARTICLE_ID VARCHAR(10),
COST NUMERIC(10, 2),
qty_should_be INT
)

DECLARE @B TABLE
(
ARTICLE_ID VARCHAR(10),
COST NUMERIC(10, 2),
qty_present INT
)

INSERT INTO @A(ARTICLE_ID, COST, qty_should_be)
SELECT 'ART0000024', 180,	100
UNION ALL
SELECT 'ART0000024', 1079,	1000

INSERT INTO @B(ARTICLE_ID, COST, qty_present)
SELECT 'ART0000024', 144,	100
UNION ALL
SELECT 'ART0000024', 1099,	1000

SELECT A.Article_ID, Sum(qty_should_be) - Sum(Qty_Present)
FROM @A A
      LEFT OUTER JOIN @B  B ON A.Article_ID = B.Article_ID
group by A.Article_ID