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?

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 ("d", {Vw_CasesPeopleOffenses_Arrestees.ArrestedOn} ,{@Next Arrest Date} )
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try a formula using the "Previous" function:

You can also look at this article for an alternative approach:

Also check out where a suggestion is made to play with the evaluation time functions (WhilePrintingrecords) and a fancy trick with 2 Crystal Variables.
Just curious why your first formula starts with
if {@Client for Group} = {@Client for Group}

 That will always be true (unless {@Client for Group} produces a null).

 As for your question, Next or Previous does seem like the obvious solution.  In your case, probably Next, since you're already using it for ArrestedOn.  Exactly where and how you use it may depend on the formulas you have, and where you're using them.

 Basically, you probably want to use Next to check some field that identifies individuals, to see if the next record is for the same person.  For example:

if not OnLastRecord
and {person ID field} = Next ({person ID field})
and {@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)

 The OnLastRecord check is because I don't know what Next () gives you if there is no next record.

 You might also want to add a test to @DateDiff, to not calculate the difference if the result of @NextArrestDate is CDateTime (0000, 00, 00, 00, 00, 00).

 A couple more observations:

 You're checking the length of ArrestedOn (after it's converted to a string).  Since ArrestedOn is apparently a datetime, the only way I see the length of the string being <= 1 is if ArrestedOn is null.  But if it's null, that test won't work.  You need to use IsNull to see if a field is null.

 Whatever you're checking for when you check the length, do you need to do the same test on Next (ArrestedOn) in @NextArrestDate ?  IOW, if it's possible that you don't want to use the ArrestedOn value in the current record, is it also possible that you don't want to use the value in the next record?

 And that leads to the next question:
 If you there are some ArrestedOn values that you don't want to use, and if you could have 3 or more records for the same person, what if some records in the middle have ArrestedOn values that you don't want to use?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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.
ChoppAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.