asked on

How to calculate a diff between two SQL tables

I have two groups of tables with similar structures. I have designed a query for each group that returns the data in the format that I want:

table1:
1      CISCO2811      420.0000
31      CISCO2921-V/K9      1266.0000
1      CISCO3925E-SEC/K9      1450.0000
4      7600-SIP-600=      7560.0000

table2:
1      CISCO2811      672.00
1      CISCO3925E-SEC/K9      2320.00
17      7600-SIP-600=      12096.00

The columns that I am concerned with are Quantity, SKU and Price. The goal is that the data in both tables should be the same (from a business process standpoint) and I need a way to evaluate whether this is a true statement and if not then to specifically list the over/short information for each table.

To further explain, considering the sample data above i'm thinking that the best way to visualize the result set would be to have two queries run; one that lists the items and qty in table1 that are not present in table2, and a second query that lists the items and qty in table2 that are not in table1.

With that in mind I would expect the following results.
result1:
31 CISCO2921-V/K9 1266.00

result2:
13 7600-SIP-600= 12096.00

Can someone help me out with one or more queries that will sort out this problem and/or suggest an alternative approach that may be better suited to the problem at hand? Thanks!

Lehause

At a simple level given the above data, to get what you want :

SELECT * FROM Table1
WHERE ProductID NOT IN (SELECT ProductID FROM Table2)

and obviously switch around tables to get the other query.
That will give you all data that is in one table and not in another.

You should also consider

SELECT T1.*
FROM Table1 T1, Table2 T2
WHERE T1.ProductID=T2.ProductID
and T1.Quantity<>T2.Quantity

This would give you a list of items whereby the items exist in both table but quantity does not match.
This could be adapted to find where the price does not match either..

hope this helps.

Emes

Are you looking for the data difference between the 2 tables when price and Qty are different for the same sKU?

select q1.sku,q1,qty,q1.price, q2,price,q2,qty
from table1 t1
inner join table2 t2 on t1.sku = t2.sku
where t1.qty <> t2.qty or t1.price <> t2.price

vdr1620

SELECT Quantity, SKU and Price, T1 as TableName
FROM Table1 T1
WHERE NOT EXISTS (SELECT ColumnName FROM Table2 T2 WHERE T1.ColumnName = T2.columnName)
UNION
SELECT Quantity, SKU and Price, T2 as TableName
FROM Table2 T2
WHERE NOT EXISTS (SELECT ColumnName FROM Table1 T1 WHERE T1.ColumnName = T2.columnName)

DanLockwood

ASKER

I put together the following query but it returns no row. I assume that this is due to the fact that the price and qty between the rows are different:

SELECT     SUM(tblProposalDetail.Quantity) AS Quantity, tblProposalDetail.ProductNumber, tblSMARTnet.Price
FROM         tblProposalDetail INNER JOIN
                      tblSMARTnet ON tblProposalDetail.ProductNumber = tblSMARTnet.ProductNumber INNER JOIN
                      tblSMARTnetServiceLevel ON tblProposalDetail.fkSMARTnet = tblSMARTnetServiceLevel.ID AND 
                      tblSMARTnet.fkSMARTnetServiceLevel = tblSMARTnetServiceLevel.ID
WHERE     (tblProposalDetail.fkProposal = 2233) AND (NOT EXISTS
                          (SELECT     COUNT(tblCiscoSCCDetail.ID) AS Quantity, tblCiscoSCCDetail.ProductNumber, tblCiscoSCCDetail.QuoteAmount
                            FROM          tblCiscoSCC INNER JOIN
                                                   tblCiscoSCCDetail ON tblCiscoSCC.ID = tblCiscoSCCDetail.fkCiscoSCC
                            WHERE      (tblCiscoSCC.fkProposal = 2233)
                            GROUP BY tblCiscoSCCDetail.ProductNumber, tblCiscoSCCDetail.QuoteAmount))
GROUP BY tblProposalDetail.ProductNumber, tblSMARTnet.Price

Open in new window

DanLockwood

ASKER

Thanks for the replys. The thing that i'm not clear on is how to have the result set show the "difference" in quantity between the tables. e.g. in the sample data above there are 4 units in table 1 and 17 units in table two so I need to see the difference of 13 units in the result set for table2.

vdr1620

try this.. changed your where clause in the Exists List

SELECT     SUM(tblProposalDetail.Quantity) AS Quantity, tblProposalDetail.ProductNumber, tblSMARTnet.Price
FROM         tblProposalDetail INNER JOIN
                      tblSMARTnet ON tblProposalDetail.ProductNumber = tblSMARTnet.ProductNumber INNER JOIN
                      tblSMARTnetServiceLevel ON tblProposalDetail.fkSMARTnet = tblSMARTnetServiceLevel.ID AND 
                      tblSMARTnet.fkSMARTnetServiceLevel = tblSMARTnetServiceLevel.ID
WHERE  (tblProposalDetail.fkProposal = 2233)
 AND NOT EXISTS
      (SELECT     COUNT(tblCiscoSCCDetail.ID) AS Quantity, tblCiscoSCCDetail.ProductNumber, tblCiscoSCCDetail.QuoteAmount
       FROM          tblCiscoSCC INNER JOIN tblCiscoSCCDetail ON tblCiscoSCC.ID = tblCiscoSCCDetail.fkCiscoSCC
       WHERE      tblCiscoSCC.fkProposal = 2233 and tblProposalDetail.ProductNumber = tblCiscoSCCDetail.ProductNumber
       GROUP BY tblCiscoSCCDetail.ProductNumber, tblCiscoSCCDetail.QuoteAmount)
GROUP BY tblProposalDetail.ProductNumber, tblSMARTnet.Price

Open in new window

ASKER CERTIFIED SOLUTION

Lehause

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanLockwood

ASKER

Thanks Lehause. Since the result sets that i'm working with are derrived and not an actual table, how do I incorporate them into your suggested query?

Lehause

You could use temporary table whereby for each derived result set :

SELECT * INTO #TempTable1
FROM .....

These would hold the data from the derived query and allow you to run :

SELECT T1.sku,T1.price Table1Price, T2.price Table2Price,
T1.price - T2.Price PriceDifference,
T1.qty Table1Qty,T2.qty Table2Qty,
T1.qty - T2.qty QtyDifference
FROM #TempTable1 T1
INNER JOIN #TempTable1 T2 on T1.sku = T2.sku
WHERE (T1.qty <> T2.qty OR T1.price <> T2.price)

However it might be better to simply create 2 Views:

Create View Table1 as
Select -- You drived query for table 1

Create View Table2 as
Select -- You drived query for table 2

Then run :

SELECT T1.sku,T1.price Table1Price, T2.price Table2Price,
T1.price - T2.Price PriceDifference,
T1.qty Table1Qty,T2.qty Table2Qty,
T1.qty - T2.qty QtyDifference
FROM Table1 T1
INNER JOIN Table2 T2 on T1.sku = T2.sku
WHERE (T1.qty <> T2.qty OR T1.price <> T2.price)

This would be dynamic and you would not have to refresh the data.

SOLUTION

vdr1620

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanLockwood

ASKER

Thanks. The output format suggested by Lehause works well for the program that needs to consume the data so the combination of both of your suggestions works out great! thanks!

DanLockwood

ASKER

One thing I just noticed in my testing is that the result set doesn't include rows that are in table1 but not in table2; e.g.
31 CISCO2921-V/K9 1266.0000

Is there a simple adjustment that can be made to catch this data?

vdr1620

You will need to use the UNION Approach as i suggested in my earlier post