Matching On Sum Of Rows - SQL Server Forums

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

Transact-SQL (2000)

Matching On Sum Of Rows

Author

Topic

yamunabgs
Starting Member

2 Posts

Posted - 2006-11-29 : 22:00:58

I need to write a sql query that will match transaction in two tables. The match should be many to many where the sum of the transactions for a particular id in one table adds up to the sum of the transactions for the same id in the other table. However, there could be extra transactions with the same id in either tables that do not form part of the match because if their amounts are added up the new sums in both table would not be equal.

For example:

In the following tables, transactions in Table A with amount 20 and 10 should match transaction with amount 30 in Table B, since all of them have the same id=1. However, transaction with amount=15 must not match anything in Table B

Table A
id Amount
-- ------
1 20
1 10
1 15

Table B
id Amount
-- ------
1 30

Is there a way for me to get the match in T-SQL?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-29 : 22:48:08

The requirements don't really make sense.

How would you know to include one transaction but not another?

CODO ERGO SUM

yamunabgs
Starting Member

2 Posts

Posted - 2006-11-29 : 23:23:16

Its like 20+10 in Table A is equal to 30 in Table B. But 15 doesn't match anything. Also 20+15 or 10+15 or 20+10+15 doesn't match any rows in Table B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 00:15:45

What about

Table A
id Amount
-- ------
1 30
1 10
1 15
1 25
1 20
1 20
1 10
1 30

Table B
id Amount
-- ------
1 40

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 00:45:26

[code]SELECT *
FROM TableA a
FUZZY JOIN TableB b ON b.ID = a.ID
HAVING SUM(b.Amount) = SUM(a.Amount)[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 01:43:06

If there are exactly 3 matches in table A for each record in table b, use this

-- prepare test data
declare @tablea table (id int, Amount int)

insert	@tablea
select	1, 20 union all
select	1, 10 union all
select	1, 15

declare @tableb table (id int, Amount int)

insert	@tableb
select	1, 30

-- do the magic
SELECT		a.ID,
		b1.p1 * a.Col1 Col1,
		b2.p2 * a.Col2 Col2,
		b3.p3 * a.Col3 Col3,
		b.Amount
FROM		(SELECT 0 p1 UNION ALL SELECT 1) b1
CROSS JOIN	(SELECT 0 p2 UNION ALL SELECT 1) b2
CROSS JOIN	(SELECT 0 p3 UNION ALL SELECT 1) b3
CROSS JOIN	(SELECT	ID, MIN(Amount) Col1, SUM(Amount) - MIN(Amount) - MAX(Amount) Col2, MAX(Amount) Col3 FROM @TableA GROUP BY ID) a
INNER JOIN	@TableB b ON b.ID = a.ID
WHERE		p1 + p2 + p3 > 0
		AND b1.p1 * a.Col1 + b2.p2 * a.Col2 + b3.p3 * a.Col3 = b.Amount

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources