asked on

Number of Days Between Records Based on Single DateTime Field

Hello Experts,

I need to calculate a datediff, but instead of having two distinct fields, I am using a single field. The goal is to find out how many days passed between arrest dates for a single person. I have a report that is grouped by persons showing arrest dates. I want to show the number of days between arrests, per person. I attempted to make a formula to return the next arrest date value per person to use in a datediff, but it is not working PER PERSON. It is calculating the difference between dates using the date for the next person...

Can you help me to get the datediff to work using a single field, per person?
~Chop

@NextArrestDate
if {@Client for Group} = {@Client for Group}
and length(totext({Vw_CasesPeopleOffenses_Arrestees.ArrestedOn}) ) > 1
then next({Vw_CasesPeopleOffenses_Arrestees.ArrestedOn})
else CDateTime (0000, 00, 00, 00, 00, 00)

@DateDiff
DateDiff ("d", {Vw_CasesPeopleOffenses_Arrestees.ArrestedOn} ,{@Next Arrest Date} )

Mlanda T

Try a formula using the "Previous" function:
{Vw_CasesPeopleOffenses_Arrestees.ArrestedOn}-previous({Vw_CasesPeopleOffenses_Arrestees.ArrestedOn})

You can also look at this article for an alternative approach:
http://customerfx.com/pages/reporting/2010/01/22/using-crystal-reports-quot-next-quot-and-quot-previous-quot-functions.aspx

Also check out http://www.crystalreportsbook.com/Forum/forum_posts.asp?TID=20293 where a suggestion is made to play with the evaluation time functions (WhilePrintingrecords) and a fancy trick with 2 Crystal Variables.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ido Millet

Use a Detail-level formula to add arrest dates into a Date Array variable.

Use that array in a Group Footer formula to calculate average number of days between arrests.

Reset the array variable in a Group Header formula.

Chopp

ASKER

Thank you ALL for the information. I have saved the whole bunch for potential use in future reports. I ended up adjusting my formula per James' advice to include: {person ID field} = Next ({person ID field})

Respectfully, Chop