Cost Per Day 1

[Jcfx]

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

 

[lbass]

You haven't shown us whether you have returned the first and last dispatch date of the year in the view, or whether that needs to be calculated. If you add the dates, do the details then repeat per date? How could a unit be dispatched before it was inservice?? Are you expecting the number of days to be a simple difference between two dates?

-LB

 

[Jcfx]

Apologies.

[1]You haven't shown us whether you have returned the first and last dispatch date of the year in the view, or whether that needs to be calculated

They need to be calculated. The view stores all dispatches by date completed. I was thinking I could use min and maxes by year? Then compare the Max Min years to yearcharged to get them placed in the details. suppress them and perform calculations between the two for my days?

[2] Are you expecting the number of days to be a simple difference between two dates?

Yes, either the difference between the first and last dispatch of the year, or the difference between the inservice date and last dispatch for a brand new unit in the first year (see below)

[3] How could a unit be dispatched before it was inservice??

Inservice occurs once in a units life time. Perhaps a better example. Unit 139 goes into service (on the asset books) on 5-27-06, doesnt matter to the powers that be that its first ever dispatch occurs on 7-24-06, for that first year of its life all costs start counting from inservice.
Unit 9 inservice date was 1-23-2000. In this example using only years 2006 - 2008 all costs are calculated from Dispatch_Date.

Don't know how else to explain it other than the Inservice_Date is a "Base" that only comes into play if the date happens to fall in the report select parameter. Which, bah I forgot to mention is hardcoded for last three years.



Julie
CRXI CE10 / RS2005 Sql DB

 

[lbass]

Try the following. I'm guessing, so if this doesn't work, please show sample data once the date fields are added into the detail section.

First insert a group #2 on year, assuming group#1 is on unit. Then create this formula:

//{@insvcdate}:
if isnull({table.inservicedate}) then
date(0,0,0) else
{table.inservicedate}

Then create a second formula and place it in a year group section:

datevar mindate;
if minimum({table.dispatchdate},{table.year}) < maximum({@insvcdate},{table.year}) then
mindate := maximum({@insvcdate},{table.year}) else
mindate := minimum({table.dispatchdate},{table.year});
maximum({table.cost4yr},{table.year})/datediff("d", mindate, maximum({table.dispatchdate},{table.year}))

-LB

 

[Jcfx]

Thank you,
That works

Appreciate your assistance.

Julie
CRXI CE10 / RS2005 Sql DB