Select Min/Max Date from date Range

[CurtR]

Hi Everyone,
I have a report that is getting too envolved for my skill level. I have 12 tables linked to get Vehicle maintance costs, part costs, labor costs etc, and now they also want mileage added to this report.
The table where the Mileage is Kept has 2 fields that I need to deal with ( if I am thinking correctly) Date and Mileage. There are weekly dates with mileage readings in the same row.( oracle 8i table with Hansen Front end) I am using a start and End date parameter for the report. I need to figure the miles used during the period between start and end.
If the paramaters are Start 6/01/2001 and End 10/31/2001 , I need to get the minimum and maximum mileage to subtract for use. This would have to be based on the date. But how do I tell crystal I want the mileage from the 6/01/2001 row and not the 6/15/2001 row for the minimum and the 10/31/2001 and not the 10/01/2001 for the maximum?
Thanks
Curt

 

[balves]

Assuming you have 2 parameters to the report, p_start_dt and p_end_dt.

You can create 2 formulas:

p_start_mileage:

if {MileageTable.Date} = {?p_start_dt}
then {MileageTable.Mileage}

p_end_mileage:

if {MileageTable.Date} = {?p_end_dt}
then {MileageTable.Mileage}

In the Report Footer after all the records are read, you can take the difference of the 2 formulas to get the mileage.

 

[kenhamady]

You will need to do grand totals of those formulas, and net the grand totals. This also assumes that all the weekly dates in the table will match the date entered for the parameter. Is that true?

Are you getting a pair of records for every vehicle and need a total for all vehicles? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[balves]

I could be (probably?) wrong, but I don't think you want grand totals. I think the mileage number week by week is the odometer reading.

So it's:

Week 1 - 10,000
Week 2 - 10,200
Week 3 - 10,500

The mileage number you want is 10,500 - 10,000 = 500.

 

[CurtR]

you are correct in that the milage in the database is the odometer reading for the week, and I must subtract the minimum odometer reading of the report period from the maximum reading of the report period to get the useage for the vehicle.
The parameters for the report are based on dates. Start and End dates.
I will have (hopefully) a odometer reading per week. So the actual date of the odometer reading will be based on Sunday Dates. Which means that the Parameter date for the report may not be the same as the odometer reading date.

Here is actual from one vehicle:
Odometer Date
69359 07/01/2001
69125 06/24/2001
68734 06/17/2001
68328 06/10/2001
68075 06/03/2001
67873 05/27/2001

If the Parameters Are for 06/01/2001 to 06/30/2001 for the report.
I need to use the odometer readings from 06/03/2001 and 06/24/2001 for the report.
Hope that explains this a little better!
Thanks
Curt

 

[kenhamady]

If you don't need it across multiple vehicles, why not:

1) Add a group by vehicle
2) Create a summary that is minimum mileage per vehicle
3) Create a summary that is maximum mileage per vehicle
4) Subtract the min from the max Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[CurtR]

I will try that now, but will that break on the parameter dates, or just pull the min / max odometer for that vehicle?
Thanks
For the Help!!

 

[CurtR]

Ken,
Tried it with the group on vehicle and it did what I thought it would and just pulled the max and min for the vehicle , not paying attention to the dates set forth in the parameter for the report.
This would be very simple if it were not for the fact of all the other information they want this report to include. Select Max(mileage) from...... : )
Thanks Curt

 

[Guest_imported]

I am having the exact same problem. I cannot get Crystal to return the latest date (of multiple dates) within a certain month.

 

[kenhamady]

If there are records in the report that shouldn't be considered in the MAX or MIN do this:

Create a @ValidMileage formula:

if {MileageTable.Date} in
{?p_start_dt} to {?p_end_dt}
then {MileageTable.Mileage} else 0


Now do the Min and Max of this field instead. It will only inlcude the ones you want. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[kenhamady]

oops, as soon as I hit post, I realized that the Minimum would pick up the zero.

You will need 2 formulas:

if {MileageTable.Date} >= {?p_start_dt}
{MileageTable.Mileage} else 99999999

if {MileageTable.Date} <= {?p_end_dt}
then {MileageTable.Mileage} else 0

Take the Min of the first formula and the Max of the second formula. Then net these two summaries to get your net Mileage. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[CurtR]

Ken, That appears to have done it. I will fully check it out and let you know. Thanks Again for all of help!
Curt

 

[CurtR]

Ok,That did it !!! Thank You Very much.
now they want to see the number of days used !
Glad it is Friday!!
Thanks Again and Have a great Weekend!!

 

[CurtR]

I have been trying to understand how to get the days used per vehicle for this report. But no matter what I do I can not get the report data correct.
This is the same report as above, based on the same tables ( 13 now).

Here is actual from one vehicle:
Unit Id Odometer Date sun mon tue wed thu fri sat
0032 69359 07/01/2001 x x x x x
0064 69125 06/24/2001 x x x x
0032 68734 06/17/2001 x x x x
0032 68328 06/10/2001 x x x x x
0064 68075 06/03/2001 x x x x x
0097 67873 05/27/2001 x x x

The database holds &quot;x&quot; for a day the vehicle used.
I need to get total days used per vehicle, I have tried a running total but I can not make it&quot; break&quot; within the dates set by the parameter dates.

I have tried a running total for each day then adding those sums together, and everything else I can think of , any ideas ??
Thanks Curt

 

[kenhamady]

In the example data you just posted, what should the total days be, and how would you calculate it manually? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[CurtR]

I would &quot;request a report for a date Range&quot; June for example.

Being that the example data aboe only has 2 vehicles with days used during the period
A June report would show

Vehicle ID Days used
0032 9
0064 9

The other vehicles data is outside the report parameter

Basicly I need to count the X's for each Vehicle between the dates set by
the {?Start Date}and {?End Date}

 

[kenhamady]

To make it simple I will assume that each date is a Sunday and that each x is in a separate field called Sun, Mon, Tue, etc.

You will need a formula like this:


If Sun = 'x' and
{Date} in Start to End then 1 else 0
+
If Mon = 'x' and
{Date}+1 in Start to End then 1 else 0
+
If Tue = 'x' and
{Date}+2 in Start to End then 1 else 0
+
....


A subtotal or grand total of this field will give you total days used. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[CurtR]

Ken,
Your assumptions are correct.
I was heading in this same direction but I was using a seperate formula for each field. Only cause I figured somewhere, somehow, someone would ask how many &quot;Sunday's or Friday's&quot; a vehicle was used. Anyway the sum of all (7) these formulas did not work.
I just tried your suggestion and I got blanks, When I changed your formula to read
if not isnull({WBVEH.SUN}) and {WBVEH.WEEKDAY} in{?start} to {?End} then 1 else 0 +
if not isnull({WBVEH.MON}) and {WBVEH.WEEKDAY} in{?start} to {?End} then 1 else 0 +
I got 0's , instead of blanks, not sure what the problem is? I really did not think that adding up these x's would be this difficult !

Ken, I want to thank you for your help
Curt

 

[CurtR]

Ken,
right after I posted last response I looked at the report deeper ( 1330 pages)
and I am getting data, Hold tight before you do anymore on this and I will let you know how it looks!!

Thanks again
Curt

 

[kenhamady]

Curt,

null values will cause the formula to choke, you can either use the global replace of nulls (file - Report options) or do an isNull check in the formula for each field. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/