Posted by figital on 07/11/06 16:15

I'm not really going to get into the schema design. I'll just assume
that you left a lot out for the post. You can do this much easier if
you have a single table to handle your debits and credits...anyway...

BEGIN TRANSACTION

DECLARE @tCredits TABLE (TransactionID INT IDENTITY(1,1), UserID INT,
Amount MONEY, Applied BIT)
DECLARE @tDebits TABLE (TransactionID INT IDENTITY(1,1), UserID INT,
Amount MONEY, Applied BIT)
DECLARE @tUsers TABLE (UserID INT)

INSERT INTO @tUsers
SELECT 192 UNION ALL
SELECT 207 UNION ALL
SELECT 212

INSERT INTO @tCredits
SELECT 192, 1, 1 UNION ALL
SELECT 192, 2, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 2, 1 UNION ALL
SELECT 212, 3, 1


INSERT INTO @tDebits
SELECT 192, 1, 1 UNION ALL
SELECT 207, 1, 1 UNION ALL
SELECT 207, 1, 1

--SELECT * FROM @tUsers
--SELECT * FROM @tCredits
--SELECT * FROM @tDebits

SELECT UserID,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tCredits WHERE UserID =
U.UserID) TotalCredits,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tDebits WHERE UserID =
U.UserID) TotalDebits,
(SELECT ISNULL(SUM(Amount), 0.0) FROM @tCredits WHERE UserID =
U.UserID)-(SELECT ISNULL(SUM(Amount), 0.0) FROM @tDebits WHERE UserID =
U.UserID) Balance
FROM @tUsers U

ROLLBACK TRANSACTION

[Back to original message]