Comparing amounts in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing amounts in two tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-20 : 09:22:23

Wayne writes "I want to compare the dollar amounts in two tables and reduce the two tables down to non-matching amounts. For example, starting with the two tables:

Table1
ID  Amt
01   44
02  100
03  100

Table2
ID  Amt
41  100
42  100
43  100

I want to reduce them down to:
Table1
ID  Amt
01   44

Table2
ID  Amt
43  100

(ie) similar amounts in the tables cancel out one another. Any thoughts would be appreciated."

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-12-20 : 09:51:08

how about linking the tables into MSAccess and using the find unmatched query wizard ??

Wayne
Starting Member

1 Post

Posted - 2001-12-20 : 10:10:52

I should elaborate.

I want to compare and eliminate dollar values only. The ID columns are unique identifiers to a table only and not links between the tables. The tables may or may not have the same number of rows.

In truth I don't care which row in Table2 is the odd one out - just that the end result show that Table1 had one $44 amount that didn't match Table2, and that Table2 had one $100 amount that didn't matchup Table1.

I'm pretty sure a straight SQL stmt. won't handle it - but feel free to prove me wrong. I'm thinking that I will need a sproc to loop through both tables line by line and would appreciate any confirmations/denials/insights.

sica
Posting Yak Master

143 Posts

Posted - 2001-12-20 : 10:15:38

You can try to create 2 temp table for every table to translate ID on the both table and then try to match them and eliminate tuple w/ common amount.I'm not sure if this is what you want but it's an idea.

CREATE TABLE #Table1
(ID INT,Amt MONEY)

CREATE TABLE #Table2
(ID INT,Amt MONEY)

CREATE TABLE #temp1
(ID INT IDENTITY(1,1),ID1 INT)

CREATE TABLE #temp2
(ID INT IDENTITY(1,1),ID2 INT)

INSERT INTO #Table1 SELECT 1,44
INSERT INTO #Table1 SELECT 2,100
INSERT INTO #Table1 SELECT 3,100

INSERT INTO #Table2 SELECT 41,100
INSERT INTO #Table2 SELECT 42,100
INSERT INTO #Table2 SELECT 43,100

INSERT INTO #temp1
SELECT #Table1.ID FROM #Table1

INSERT INTO #temp2
SELECT #Table2.ID FROM #Table2

SELECT ID1,ID2
FROM #temp1
INNER jOIN #temp2 ON #temp1.ID = #temp2.ID

SELECT *
FROM #Table1
INNER JOIN #temp1 ON #temp1.ID1 = #Table1.ID
INNER jOIN #temp2 ON #temp1.ID = #temp2.ID
INNER JOIN #Table2 ON #temp2.ID2 = #Table2.ID
WHERE #temp1.ID = #temp2.ID AND #Table1.Amt != #Table2.Amt

Sica

izaltsman
A custom title

1139 Posts

Posted - 2001-12-20 : 11:45:42

I think that the solution sica offered is close, but might run into problems if test datasets had more than two distinct amounts(disclaimer: I could be wrong about this, so try his solution first --it is definitely a lot more straight forward than mine). Here is my cut at it:


--setup test data

create table table1(id varchar(5), amt int)
create table table2(id varchar(5), amt int)

insert into table1 values ('01',   44)
insert into table1 values ('02',  100)
insert into table1 values ('03',  100)

insert into table2 values ('41',  100)
insert into table2 values ('42',  100)
insert into table2 values ('43',  100)

go


-- create temp tables (to hold ids that will not be deleted)
create table #t1_keepers (id varchar(5))
create table #t2_keepers (id varchar(5))

-- Figure out ids to keep in table1
insert into #t1_keepers
select src.id from 
(select  COUNT(*) as rank, table1.id, table1.amt 
from 
	table1 INNER JOIN table1 r ON (table1.amt = r.amt AND table1.id <= r.id)
group by table1.id, table1.amt) as src 
INNER JOIN 
(
select a.amt, CASE WHEN (a.cnt - isnull(b.cnt, 0)) < = 0 THEN 0 ELSE (a.cnt - isnull(b.cnt, 0)) END as num_to_keep
FROM 
(select amt, count(*) as cnt from table1
group by amt) as a
LEFT JOIN 
(select amt, count(*) as cnt from table2
group by amt) b
	ON (a.amt = b.amt)
) as qual
ON (src.amt = qual.amt)
WHERE (src.rank <= qual.num_to_keep )

-- Figure out ids to keep in table2
insert into #t2_keepers
select src.id from 
(select  COUNT(*) as rank, table2.id, table2.amt 
from 
	table2 INNER JOIN table2 r ON (table2.amt = r.amt AND table2.id <= r.id)
group by table2.id, table2.amt) as src 
INNER JOIN 
(
select a.amt, CASE WHEN (a.cnt - isnull(b.cnt, 0)) < = 0 THEN 0 ELSE (a.cnt - isnull(b.cnt, 0)) END as num_to_keep
FROM 
(select amt, count(*) as cnt from table2
group by amt) as a
LEFT JOIN 
(select amt, count(*) as cnt from table1
group by amt) b
	ON (a.amt = b.amt)
) as qual
ON (src.amt = qual.amt)
WHERE (src.rank <= qual.num_to_keep)

--Delete unwanted records
DELETE table1 FROM table1 t1
left join #t1_keepers as t1k on (t1.id = t1k.id)
WHERE t1k.id IS NULL

--Delete unwanted records
DELETE table2 FROM table2 t2
left join #t2_keepers as t2k on (t2.id = t2k.id)
WHERE t2k.id IS NULL

select * from table1
select * from table2

drop table #t1_keepers
drop table #t2_keepers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources