SQL User Defined Function question

Question

Hello all,

I'm having trouble getting my user defined function operating properly. I am running on SQL Server 2000.

I am trying to return a table of all users that have an even balance in a "BillingTransactions" table. Our transactions are specified by the RecordType field; 0 for purchase, 1 for payment. So what I'm trying to do is get a list of all users where the sum of each transaction with RecordType 0 equals the sum of each transaction with RecordType 1. This is what the internal portion of my function looks like now:

    SELECT DISTINCT UserName FROM BillingTransactions
WHERE (SELECT SUM(AMOUNT) 
    FROM BillingTransactions 
    WHERE [BillingTransactions].[RecordType]= 0 
    AND  
    [BillingTransactions].[UserName]= UserName) 
    =
    (SELECT SUM(AMOUNT) 
    FROM BillingTransactions 
    WHERE [BillingTransactions].[RecordType]= 1 
    AND  
    [BillingTransactions].[UserName]= UserName)

I have a feeling this is not the most efficient way to do this... Is there any other way you can see how to go about this? Thanks!

Answer 1

Try something like this instead:

select a.username
from (select username, sum(amount) totalAmount
     from BillingTransactions
     where RecordType = 0
     group by username
     ) a
join (select username, sum(amount) totalAmount
     from BillingTransactions
     where RecordType = 1
     group by username
     ) b on b.username = a.username and b.totalAmount = a.totalAmount

Answer 2

As with any SQL query, the efficiency will be driven by the actual layout of your data (table structure, index structure) just as much as by the text of the query. Here is a version of the same query that expresses the same request, but less verbatim and possibly more efficient:

SELECT UserName 
FROM BillingTransactions
GROUP BY UserName
HAVING 0 = SUM(
  CASE RecordType 
  WHEN 1 THEN AMOUNT 
  WHEN 0 THEN -AMOUNT 
  ELSE 0
  END);