Summing up values in SQL does not work

Question

I have a table [Transaction] which is designed to store values of transactions of players who play backgammon with money. The house can give credit (buy in the game and pay later) or debit (pay cash in hand). There are four types of transactions (Debit, Credit) and ('Credit Settlement' - customer pays back some or all of what he owes, and 'Debit Settlement' - payment by the house if the house owes money to the player). What I'm trying to do is to find the balance. I have tried a lot of sql statements but I'm always missing something and the balance is not correct.

A simple scenario is: Customer walks in, buys in for 300 in credit. He cashes out 100, so he owes 200 to the house. He comes the next day, buys in for 100 credit (so now he owes 300) and ends up cashing out 1000. The house pays him 500 but owes him 200 more. The house pays 200 to the customer so this is stored as 'Debit Settlement'.

enter image description here

Here is what I have so far but the results are not always correct:

SELECT     SUM(Cashout)  - ((SELECT     COALESCE (SUM(Paid), 0) AS Expr1
                            FROM          [Transaction]
                            WHERE      (Type = 'Credit Settlement'))  + SUM(Buyin) +
                          (SELECT     COALESCE (SUM(Paid), 0) AS Expr1
                            FROM          [Transaction] AS Transaction_3
                            WHERE      (Type = 'Debit Settlement') AND (Paid IS NOT NULL))) AS OutstandingDebit
FROM         [Transaction] AS Transaction_1
WHERE     (Type <> 'Debit Settlement') AND (Cashout >= 0) AND (CustomerID = 132)

Where does CustomerID fit in and, more importantly, why isn't it referenced in the two subqueries? — Tim Rogers
@Tim Rogers There is a customerID column on the table, but it does not affect how the operation of the select statement works. You could assume it will be 132. — nec tso
@Justin TransID: 190 Customer bought in 100 in Credit and Cashed out 50, so he owes 50. However we paid him 50 so he owes 100. TransID: 197 He bought in for 100 and lost. He still owes 100. TransID: 198 He paid us back the 100 so balance is 0. TransID 199. Customer paid us by mistake 50 for Cr Settlement, so we owe him +50. TransID: 200 He buys in for 200 Credit, he wins 250 (450-200), but we forgot it's a credit transaction so we pay him 200 more. So, he owes us -200 and we owe him 50 from TransID 199, so he owes us 150. TransID:201 He pays part of what he owes us 50, so he owes us 100. — nec tso
@nectso My point was that your two subqueries are going to sum everything in the table of a certain type. That can't be right. — Tim Rogers

alwaysVBNET alwaysVBNET · Accepted Answer · 2013-06-12T19:15:16

Try this. I believe it works. The balance should be -200. I'm sure though someone can edit it and optimize it or make it look more elegant! @Justin , @Tim Rogers , @nec tso

 SELECT (
 (SELECT coalesce(SUM(cashout),0)- 
                        ((select coalesce(sum(Buyin),0) from [Transaction] where TYPE='Credit' and CustomerID=132)
                         + (select coalesce(sum(Paid),0) from [Transaction] where TYPE='Credit' and CustomerID=132))


FROM [transaction]
WHERE TYPE='Credit'
AND CustomerID=132
)
-------------------
+
(
(SELECT coalesce(SUM(cashout),0)
                    - (select coalesce(sum(Paid),0) from [Transaction] where TYPE='Debit' AND Cashout>buyin and CustomerID=132) 
                    +  (select coalesce(sum(Cashout),0)- (select coalesce(sum(PAID),0) from [Transaction] where TYPE='Debit' AND Cashout<buyin and CustomerID=132)
                             from [Transaction] where TYPE='Debit' AND Cashout<Buyin and CustomerID=132)
                    +  (select coalesce(sum(Cashout),0)- (select coalesce(sum(PAID),0) from [Transaction] where TYPE='Debit' AND Cashout=buyin and CustomerID=132)
                             from [Transaction] where TYPE='Debit' AND Cashout=Buyin and CustomerID=132)
FROM [Transaction]
WHERE CustomerID=132
AND TYPE='Debit' 
AND Cashout>buyin )
)
--------------
-
(
select coalesce(sum(Paid),0)
from [Transaction] 
where type='Debit Settlement'
AND CustomerID =132
)
--------------
+
(
select coalesce(sum(Paid),0)
from [Transaction] 
where type='Credit Settlement'
AND CustomerID =132
)
);

Summing up values in SQL does not work

1 Answers