SQL Server Performance Forum – Threads Archive
SP Question
I need to write a Stored Procedure that returns only select client records. I have a Client table and a Revenue table. Basically, what I need is for the stored procedure to group together all the clients account transactions that exists in the Revenue table and evaluate whether the account has a credit for an ending balance. For example, Client_A ServiceDate TranDate Revenue Payment Transfer Adjustment1/1/06 1/1/06 $100 $50 $0 $0
1/1/06 1/20/06 $0 $50 $0 $0
2/22/06 2/22/06 $100 $0 $0 $0
2/22/06 3/1/06 $0 $150 $0 $0 My formula for the ending balance would be Sum of Revenue – Sum of Payments – Sum of Transfers -Sum of Adjustement. My problem is that I don’t know if I can get the Stored Procdure to group together all transactions that are related to a client’s account, then evaluate the ending balance, and then based on the ending balance only return client records that have a Credit balance or a negative balance. Can this be done and if so what would the SP look like?
Can you post some sample data and the result you want? Madhivanan Failing to plan is Planning to fail
select client, ending_balance = sum(Revenue) – sum(Payment) – sum(Transfer) – sum(Adjustment)
from table
group by client
having sum(Revenue) – sum(Payment) – sum(Transfer) – sum(Adjustment) <> 0 KH
]]>