How to calculate a diff between two SQL tables
Posted on 2010-09-02
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:
1 CISCO2811 420.0000
31 CISCO2921-V/K9 1266.0000
1 CISCO3925E-SEC/K9 1450.0000
4 7600-SIP-600= 7560.0000
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.
31 CISCO2921-V/K9 1266.00
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!