Posted by danielbair on 07/11/06 15:34

I am trying to tally up information from two tables, but I am running
in to trouble and I can't seem to figure this out. I have a
CreditsTable and a DebitsTable and I am trying to create a querry that
sums up the info from each per UserID and returns TotalCredits,
TotalDebits, and a Ballance.


CreditsTable
UserID Ammount Applied
+----------+----------+----------+
| 192 | 1 | True |
| 192 | 2 | True |
| 207 | 1 | True |
| 207 | 1 | True |
| 207 | 2 | True |
| 212 | 3 | True |

DebitsTable
UserID Ammount Applied
+----------+----------+----------+
| 192 | 1 | True |
| 207 | 1 | True |
| 207 | 1 | True |


***This is the Function I have tried, but it doesn't return the correct
results

ALTER FUNCTION [dbo].[BallanceTotals]()
RETURNS TABLE
AS
RETURN
(
SELECT DISTINCT
dbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,
SUM(dbo.DebitsTable.Ammount) AS TotalDebits,
SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS Ballance
FROM
dbo.CreditsTable FULL OUTER JOIN
dbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserID
WHERE
(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)
GROUP BY
dbo.CreditsTable.UserID
)


*** This is what it returns, it is not adding things up correctly (it
looks like it is adding NULL values as 1 instead of 0 or something)

BallanceTotals
Total Total
UserID Credits Debits Ballance
+----------+----------+----------+----------+
| 192 | 3 | 2 | 1 |
| 207 | 4 | 3 | 1 |
| 212 | 3 | | |


*** This is what I want it to return!

BallanceTotals
Total Total
UserID Credits Debits Ballance
+----------+----------+----------+----------+
| 192 | 3 | 1 | 2 |
| 207 | 4 | 2 | 2 |
| 212 | 3 | 0 | 3 |


I would really appreciate some help in getting this to work correctly!

-Daniel

[Back to original message]