Subtract two SQL Tables by Join Query

SQL 2008 :-

I have two SQL Tables of same column names. I want to subtract Table 2 [SumOCPO]  from Table 1 [SumTR]

i.e) Output = SumTR - SumOCPO - Based on Common Column Name : [Generic Code].

I have attached sample 10 rows of each table in an excel sheet and projected output in the same excel sheet.

If experts can come up with logical query will be greatly helpful. This excel sheet is easy to import to SQL Database and can be tested very easily !!!
Subtract.xls
chokkaStudentAsked:
Who is Participating?
 
SharathData EngineerCommented:
try this.
SELECT ISNULL(t1.[Generic Code], t2.[Generic Code]) [Generic Code], 
       ISNULL(t1.[NDC], t2.[NDC])                   [NDC], 
       ISNULL(t1.Qty, 0) - ISNULL(t2.Qty, 0)        Qty 
  FROM SumTR t1 
       FULL JOIN SumOCPO t2 
         ON t1.[Generic Code] = t2.[Generic Code] 

Open in new window

0
 
sventhanCommented:
try this... replace your the table names according to your need

SELECT * FROM (
SELECT * FROM
(
SELECT * FROM tablea
WHERE EXISTS ( SELECT 1 FROM TABLEb WHERE TABLEA.primarykey = TABLEB.primarykey)
EXCEPT
SELECT * FROM tableb
WHERE EXISTS ( SELECT 1 FROM TABLEa WHERE TABLEA.primarykey = TABLEB.primarykey)
) temp1
UNION
SELECT * FROM
(
SELECT * FROM tableb
WHERE EXISTS ( SELECT 1 FROM TABLEa WHERE TABLEA.primarykey = TABLEB.primarykey)
EXCEPT
SELECT * FROM tablea
WHERE EXISTS ( SELECT 1 FROM TABLEb WHERE TABLEA.primarykey = TABLEB.primarykey)
)temp2
) temp
0
 
sventhanCommented:
after changing the table names it should look something like this...
SELECT * FROM (
SELECT * FROM 
(
SELECT * FROM SumTR
WHERE EXISTS ( SELECT 1 FROM SumOCPO WHERE SumTR.primarykey = SumOCPO.primarykey)
EXCEPT 
SELECT * FROM SumOCPO
WHERE EXISTS ( SELECT 1 FROM SumTR WHERE SumTR.primarykey = SumOCPO.primarykey)
) temp1
UNION 
SELECT * FROM 
(
SELECT * FROM SumOCPO
WHERE EXISTS ( SELECT 1 FROM SumTR WHERE SumTR.primarykey = SumOCPO.primarykey)
EXCEPT 
SELECT * FROM SumTR
WHERE EXISTS ( SELECT 1 FROM SumOCPO WHERE SumTR.primarykey = SumOCPO.primarykey)
)temp2
) temp

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
SharathData EngineerCommented:
try FULL JOIN.
SELECT ISNULL(t1.[Generic Code], t2.[Generic Code]) [Generic Code], 
       ISNULL(t1.[NDC], t2.[NDC])                   [NDC], 
       ISNULL(t1.Qty, 0) - ISNULL(t2.Qty, 0)        Qty 
  FROM SumTR t1 
       FULL JOIN SumOCPO t2 
         ON t1.[Generic Code] = t2.[Generic Code] 
            AND t1.[NDC] = t2.[NDC]

Open in new window

0
 
chokkaStudentAuthor Commented:
Based on # 05/04/11 12:01 PM, ID: 35691177

I am getting WRONG OUTPUT  - I have attached the output also ..!

Let me try next two queries and report you !!!


Every query i execute, i generate an excel for your reference ..!


SELECT * FROM (
SELECT * FROM 
(
SELECT * FROM SumTRTest
WHERE EXISTS ( SELECT 1 FROM SumOCPOTest WHERE SumTRTest.[Generic Code] = SumOCPOTest.[Generic Code])
EXCEPT 
SELECT * FROM SumOCPOTest
WHERE EXISTS ( SELECT 1 FROM SumTRTest WHERE SumTRTest.[Generic Code] = SumOCPOTest.[Generic Code])
) temp1
UNION 
SELECT * FROM 
(
SELECT * FROM SumOCPOTest
WHERE EXISTS ( SELECT 1 FROM SumTRTest WHERE SumTRTest.[Generic Code] = SumOCPOTest.[Generic Code])
EXCEPT 
SELECT * FROM SumTRTest
WHERE EXISTS ( SELECT 1 FROM SumOCPOTest WHERE SumTRTest.[Generic Code] = SumOCPOTest.[Generic Code])
)temp2
) temp

Open in new window

WrongOutput.xls
0
 
chokkaStudentAuthor Commented:
Sarath .. We are pretty close ..!

Please find the Excel Sheet .. you will understand the issue !!!



Sarath.xls
0
 
chokkaStudentAuthor Commented:
In my excel sheet : subtract.xls

has two tables.

I kept only 10 rows on each table to make it easier for our calculation purpose.

Please export to SQL Database and run the query .. it will be easy to understand !!!

 SumTR - SumOCPO  = Output
0
 
chokkaStudentAuthor Commented:
sventhan:  I am sorry .. still i am getting the Same Output which i posted on the Comment Id #  ID: 35691302

Sarath's logic is pretty close ..! We need to retune it and make it perfect !!!
0
 
wdosanjosCommented:
Please try the following:

Select tr.[Generic Code], tr.NDC, tr.[Drug Name], tr.Qty - ISNULL(oc.Qty,0) Qty
  From SumTR tr
       Left Outer Join SumOCPO oc On oc.[Generic Code] = tr.[Generic Code]
Union
Select ocpo.[Generic Code], ocpo.NDC, ocpo.[Drug Name], ocpo.Qty
  From SumOCPO ocpo
 Where NOT EXISTS (Select 1 From SumTR Where SumTR.[Generic Code] = ocpo.[Generic Code])

Open in new window

0
 
chokkaStudentAuthor Commented:
Sharath 123:- Code is 100 % Perfect.

Thank you Sharath ..! Thank you for your logic !!
It's amazing , incredible !!!
0
 
chokkaStudentAuthor Commented:
Sharath 123:- Code is 100 % Perfect.

Thank you Sharath ..! Thank you for your logic !!
It's amazing , incredible !!!
0
 
wdosanjosCommented:
@chokka note that the accepted question returns negative values for rows coming from SumCOPO alone. The posted spreadsheet indicated that those should be positive values.
0
 
8080_DiverCommented:
Try this instead of the accepted solution.
SELECT  COALESCE(T1.GenericCode, T2.GenericCode) GenericCode
       ,COALESCE(T1.NDC, T2.T1.NDC) NDC
       ,COALESCE(T1.DrugName, T2.DrugName) DrugName
       ,CASE WHEN COALESCE(T1.T1.SumTR, 0) = 0)
             THEN COALESCE(T2.Qty, 0
             ELSE (COALESCE(T1.T1.SumTR, 0) - COALESCE(T2.Qty, 0) 
        END SumOCPO
FROM   SumTR T1
FULL OUTER JOIN SumOCPO T2
ON T1.GenericCode = T2.GenericCode;

Open in new window

0
 
8080_DiverCommented:
Oops, 1 too many )'s in one place and 1 too few in another! :-/
SELECT  COALESCE(T1.GenericCode, T2.GenericCode) GenericCode
       ,COALESCE(T1.NDC, T2.T1.NDC) NDC
       ,COALESCE(T1.DrugName, T2.DrugName) DrugName
       ,CASE WHEN COALESCE(T1.T1.SumTR, 0) = 0
             THEN COALESCE(T2.Qty, 0)
             ELSE COALESCE(T1.T1.SumTR, 0) - COALESCE(T2.Qty, 0) 
        END SumOCPO
FROM   SumTR T1
FULL OUTER JOIN SumOCPO T2
ON T1.GenericCode = T2.GenericCode;

Open in new window

0
 
SharathData EngineerCommented:
If that is the case, I will try this.
SELECT ISNULL(t1.[Generic Code],t2.[Generic Code]) [Generic Code],
       ISNULL(t1.[NDC],t2.[NDC])                   [NDC],
       ISNULL(t1.[Drug Name],t2.[Drug Name]) [Drug Name],
       (ISNULL(t1.Qty,0) - ISNULL(t2.Qty,0)) * CASE WHEN t1.[Generic Code] IS NOT NULL THEN 1 ELSE -1 END Qty 
  FROM SumTR t1 
       FULL JOIN SumOCPO t2 
         ON t1.[Generic Code] = t2.[Generic Code] 

Open in new window

0
 
8080_DiverCommented:
Sharath_123,

(ISNULL(t1.Qty,0) - ISNULL(t2.Qty,0)) * CASE WHEN t1.[Generic Code] IS NOT NULL THEN 1 ELSE -1 END Qty

That is a somewhat convoluted way to get there from here.  Did you look at the query I posted?
0
 
chokkaStudentAuthor Commented:
Hello wdosanjos: Your Query is also correct

i,e - Comment ID: 35691548

I decided to go for Sarath - ID: 35691624  ; because, i need to get NEGATIVE Values

Let's say an example

100X - 10X  =  90X
10X - 100X  =  -90X

100X - 10Y = We can't subtract. In that scenario, i just need to return -10Y

That's wat happened. Sharat query is absolutely correct and perfect logic !!!!
0
 
chokkaStudentAuthor Commented:
Experts, I thank you all for helping me. But i was looking for the logic as per our requirement. Sarath's logic suited my requirement. Thank you all for your help !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.