[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# How to calculate a diff between two SQL tables

Posted on 2010-09-02
Medium Priority
464 Views
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!
0
Question by:DanLockwood
• 5
• 4
• 3
• +1

LVL 1

Expert Comment

ID: 33588596
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.
0

LVL 14

Expert Comment

ID: 33588631
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
0

LVL 16

Expert Comment

ID: 33588657
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)
0

Author Comment

ID: 33588678
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
``````
0

Author Comment

ID: 33588989
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.
0

LVL 16

Expert Comment

ID: 33589071
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
``````
0

LVL 1

Accepted Solution

Lehause earned 1200 total points
ID: 33589088
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)

Note this will only list records that exist in both tables.
Consider outer join if for example you want to include all items in Table 1 that do not exist in Table 2.
0

Author Comment

ID: 33589174
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?
0

LVL 1

Expert Comment

ID: 33589304
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.
0

LVL 16

Assisted Solution

vdr1620 earned 800 total points
ID: 33589331
If you want to use Lehause Code.... try using a CTE's as below

WITH Table1 AS
(
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)
)
, Table2 AS
(
Select list that will get the data from 2nd table
)

SELECT T1.ColumnName,T1.ColumnName2            ----Lehause SQL

0

Author Comment

ID: 33589458
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!
0

Author Comment

ID: 33590825
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?
0

LVL 16

Expert Comment

ID: 33590843
You will need to use the UNION Approach as i suggested in my earlier post
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
###### Suggested Courses
Course of the Month9 days, 1 hour left to enroll

#### 590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.