Solved

How to calculate a diff between two SQL tables

Posted on 2010-09-02
13
445 Views
Last Modified: 2012-05-10
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
Comment
Question by:DanLockwood
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 1

Expert Comment

by:Lehause
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

by:Emes
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

by:vdr1620
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

by:DanLockwood
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

Open in new window

0
 

Author Comment

by:DanLockwood
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

by:vdr1620
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

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Accepted Solution

by:
Lehause earned 300 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

by:DanLockwood
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

by:Lehause
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

by:vdr1620
vdr1620 earned 200 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

by:DanLockwood
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

by:DanLockwood
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

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now