Computed Columns?

[ebunt]

I have a table that populates fields based on a case statement. I have an additional field that is derived from the case fields, and I need to find a way to calculated the derived field. For example,

billed_fee = CASE WHEN record_code = 21 OR
record_code = 30 AND assoc_record_code = 21) THEN SUM(invoice_amount + adjustment_amount) ELSE 0 END,

paid_fee = CASE WHEN record_code = 11 OR
record_code = 30 AND assoc_record_code = 11) THEN SUM(payment_amount + adjustment_amount) ELSE 0 END,

** This is what I want to do **
fee_due = billed_fee - paid_fee

Thanks in advance.

 

[crystalized]

I have done something similar and what you need is a case statement for the fee due that makes combinations of the other 2 cases

So what you need to define is the logic that drives fee-due. What I mean is what combination of record code, and assoc record code values cause fee due to be calculated in a certain way.

You probably have 4 When possibilities
1. 0 - paid fee calculated amount
2. 0 - 0
3. billed fee calculated amount - 0
4. billed fee calculated amount - paid fee calculated amount

All of these may not apply but basically all the ones that you do want to cover the possibility of need to be different When sections of a case statement for calculating the amount

fee_due = CASE WHEN <possibility1> Then sum 0 - (payment_amount + adjustment amount)
wHEN <possibility3> then invoice_amount + adjustment amount
WHEN <possibility4> then invoice_amount - payment amount
ELSE 0 END


Of course you will replace the possibility entries in the statement with the set of conditions that satisfy your needs.

I hope this helps
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[ebunt]

Thanks for the help Crystal, that is exactly what I was looking to do.