Solved

How to calculate a diff between two SQL tables

Posted on 2010-09-02
13
454 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 42
MS SQL BCP Extra Lines Between Records 2 24
sql 2016 Integration Service connecting to 2012 3 32
tempdb log keep growing 7 34
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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