>Hi everybody,
>
>I'm trying to find open accounts that have calculated balance not matching the current balance in the Trans table.
>
>Here is my current select statement:
>
>

>SELECT Trans.yReferral_balance_amount, Trans.yCurrent_balance_amount, ;
>	(SELECT SUM(Payments.yAmount_Paid) as yBalance ;
>	FROM Payments inner join Trans Trans1 ;
>	on Payments.cTrans_fk = Trans1.cTrans_pk ;
>	WHERE Payments.cTrans_fk = Trans.cTrans_pk ;
>	and Payments.dPosting_date > Trans1.tDate_received) ;
>	as yTotalPayments ;	
>   FROM TRANS ;
>   WHERE Trans.cResolution_Codes_fk is NULL into cursor curAllBalances nofilter

>
>The Calculated balance equals Referral Balance - All payments after received date.
>
>I'm not sure how can I add this calculation into this SQL. Of course, I can do this in two steps, but is there a way to do in one step?
>
>Thanks in advance.

try

* the data space
SELECT Trans1.cTrans_pk ;
, MAX(Trans.yReferral_balance_amount) yReferral_balance_amount;
, MAX(Trans.yCurrent_balance_amount) yCurrent_balance_amount  ;
, SUM(Payments.yAmount_Paid) as yTotalPayments  ;
FROM Trans LEFT JOIN Payments ;
ON Payments.cTrans_fk = Trans1.cTrans_pk ;
   AND Payments.dPosting_date > Trans.tDate_received;
WHERE Trans.cResolution_Codes_fk is NULL ;
GROUP BY 1;
into cursor curBalances


* only ALL balances
SELECT Trans1.cTrans_pk ;
, MAX(Trans.yReferral_balance_amount) yReferral_balance_amount;
, MAX(Trans.yCurrent_balance_amount) yCurrent_balance_amount  ;
FROM Trans JOIN Payments ;
ON Payments.cTrans_fk = Trans1.cTrans_pk ;
   AND Payments.dPosting_date > Trans.tDate_received;
WHERE Trans.cResolution_Codes_fk is NULL ;
GROUP BY 1;
HAVING yReferral_balance_amount = SUM(Payments.yAmount_Paid); 
into cursor curAllBalances