I have a resource table; each resou

[xsw1971]

I have a resource table; each resource may have a cost associated with it (some cost fields are NULL). Whenever that cost changes, a trigger fires and inserts into a cost_history table a record of the resource_id, the date the cost changed, and the OLD cost amount. Not all resources have a record in the cost_history table, and some resources have several records in the cost_history table.

I have a rather complex report that I run each month that determines what to charge a customer, based on the number of days they used a resource and what its cost is (I use date parameters to determine the time span.) Normally it's run using current info so I never access the cost_history table, however I need to run the report using cost values before 1/1/2002. I don't want to change the way my report is grouped, summarized, sorted, etc. I just want to calculate the value based on the resource's cost as of 12/31/2001. The logic I have is below:

FinalCost NUMBER
IF resource.resource_id = cost_history.resource_id
THEN
IF resource.cost = (max)cost_history.cost
THEN FinalCost = resource.cost
ELSE FinalCost = cost_history.cost
ELSE
FinalCost = resource.cost

How do I account for NULL values? If I use the dafault value for NULL fields, then I don't have any records that meet the first IF criteria.

I don't know how to put this all into a formula so that when my invoice is calculating the Total Cost for each resource, it knows which "FinalCost" to use.

Thanks in advance for any help you can provide!
JennieM

 

[dgillz]

you can test for nulls. If Isnull({Fieldname}) then <<Value or expression>> else <<logic for non-nulls>>

let me know if oyu have any questions. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office