Subtract two SQL Tables by Join Query

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

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

Select allOpen in new window

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]

Select allOpen in new window

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

Select allOpen in new window

WrongOutput.xls

Sarath .. We are pretty close ..!

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



Sarath.xls

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

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 !!!

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])

Select allOpen in new window

Sharath 123:- Code is 100 % Perfect.

Thank you Sharath ..! Thank you for your logic !!
It's amazing , incredible !!!

Sharath 123:- Code is 100 % Perfect.

Thank you Sharath ..! Thank you for your logic !!
It's amazing , incredible !!!

@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.

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;

Select allOpen in new window

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;

Select allOpen in new window

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] 

Select allOpen in new window

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?

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 !!!!

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 !!