I need to come up with a formula that calculates cost by day.
Currently I have the following details pulled from a view.
Report is grouped by Unit.
Detail
YearCharged Labor Parts Cost4Yr CostPrDay
2006 162.50 16.550 179.00
2007 880.75 344.94 1225.69
2008 293.07 378.19 671.26
GroupTotal 1336.32 739.63 2075.95
I have two other fields from another view linked by Unit, Inservice_Date and Dispatch_Date
I need the cost per day for the unit using the following criteria:
For each year caculate the cost per day starting with the first Dispatch_Date of the year and ending with the last Dispatch_Date of the year unless the Inservice_Date falls in the middle, then use the inservice_date as my start date.
For the example above, this unit was placed inservice on 5-27-06. Last date dispatched for 2006 was 11-24-06, 2007 dispatch started on 01-4-07 and ended on 12-22-07. 2008 started on 1-12-08 and so far the last date dispatched was 6-22-08.
How can I do this?
Thank you
Julie
CRXI CE10 / RS2005 Sql DB