Complex Join - 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)

Complex Join

Author

Topic

thanvi
Starting Member

11 Posts

Posted - 2007-08-07 : 15:42:38

Hello,

I have two tables:
payments table with columns transid and amount

credits table with creditid, transid and amount

for a payment there can be multiple credits.
The following is the data in payments table:
transid Amount
1 100
2 200
3 300
4 400
5 500
6 200

The following is the data in credits table:

creditid transid Amount
1 1 20
2 1 50
3 2 200
4 3 500
5 6 190

I need a query that gives the following output with status of less than credits if the sum of amount in credits associated with the transid is less than amount in payments:
transid Amount Status
1 100 Less than credits
2 200
3 300
4 400
5 500
6 200 Less than credits

Thanks in advance.
Thanvi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-07 : 18:36:42

I think this will work for you:

DECLARE @Payment TABLE (transid int, Amount int)

INSERT @Payment
SELECT 1, 100
UNION ALL SELECT 2, 200
UNION ALL SELECT 3, 300
UNION ALL SELECT 4, 400
UNION ALL SELECT 5, 500
UNION ALL SELECT 6, 200

DECLARE @Credit TABLE(creditid int, transid int, Amount int)

INSERT @Credit
SELECT 1, 1, 20
UNION ALL SELECT 2, 1, 50
UNION ALL SELECT 3, 2, 200
UNION ALL SELECT 4, 3, 500
UNION ALL SELECT 5, 6, 190

SELECT
	Payment.TransID,
	COALESCE(SUM(Payment.Amount), 0),
	CASE
		WHEN SUM(Credit.Amount) < SUM(Payment.Amount) THEN 'Less Than Credits'
		ELSE ''
	END AS Status
FROM
	@Payment AS Payment
LEFT OUTER JOIN
	@Credit AS Credit
	ON Credit.TransID = Payment.TransID
GROUP BY
	Payment.TransID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 23:03:29

[code]SELECT p.transid, p.amount,
status = CASE WHEN c.amount < p.amount
THEN 'less than credits'
ELSE ''
END
FROM @payment p LEFT JOIN
(
SELECT transid, amount = SUM(amount)
FROM @credit
GROUP BY transid
) c
ON p.transid = c.transid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources