Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Date time difference! Post Reply Post New Topic
Author Message
snapper
Newbie
Newbie


Joined: 18 Dec 2008
Online Status: Offline
Posts: 11
Quote snapper Replybullet Topic: Date time difference!
    Posted: 19 Mar 2009 at 2:01pm
Hey guys!
 
This is probably pretty easy buy I cant figure it out.
 
I have a create date and time and a resolved date and time.
I need the difference in a "Days:Hours:Min" format. (I Managed to string together some date dif formulas to get  the format)
 
The problem though is  I need it to be able to be evaluated so I can produce a Min, Max, Avg.
 
Any help would be awesome!
Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8774
Quote DBlank Replybullet Posted: 19 Mar 2009 at 7:07pm
Did you end of with a text string?
My initial thought for min and max is to just do a datediff on minutes and use that formula for those. FOr the average I think you could use the same thing but then convert it back into the format you want.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4267
Quote lockwelle Replybullet Posted: 20 Mar 2009 at 6:48am
I wouldn't worry about the displayed value...initially.  Do all of you calculations on your original fields then translate it for the display....
 
So for your Average it might be that you write a formula like:
totext(avgerage(field, group), "dd:hh:ss")
 
just drop the formula where desired.  I would think that this would work...at least it might provide a starting point for the solution.
IP IP Logged
snapper
Newbie
Newbie


Joined: 18 Dec 2008
Online Status: Offline
Posts: 11
Quote snapper Replybullet Posted: 20 Mar 2009 at 10:06am
I think my issues is I am tryin gto have a cross tab calculate the average and it doesn't know what its looking at.  I think I need to do th ecalculation in the formula then post it to a table!  Thanks for the tips guys!
IP IP Logged
snapper
Newbie
Newbie


Joined: 18 Dec 2008
Online Status: Offline
Posts: 11
Quote snapper Replybullet Posted: 23 Mar 2009 at 1:50pm
so I have been workign with this......how do I do a datediff then find the minimum in the same formula?

Edited by snapper - 23 Mar 2009 at 1:50pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8774
Quote DBlank Replybullet Posted: 23 Mar 2009 at 2:08pm
Not sure what you mean by finding the minimum within the "same formula".
The formula is to find the difference between 2 items then use the Summary function on that formula to find the minimum with that grouping that you want or for the entire report.
Is this what you mean?
 
datediff in minutes as "MinutesDifference":
datediff("n",{table.DATE1},{table.DATE2})
 
Summaries for entire report (not group levels):


Edited by DBlank - 23 Mar 2009 at 2:19pm
IP IP Logged
snapper
Newbie
Newbie


Joined: 18 Dec 2008
Online Status: Offline
Posts: 11
Quote snapper Replybullet Posted: 23 Mar 2009 at 3:18pm
Thats what I have going on.......but I strung them togther as I need the min, max, and avg all evaluated together  (I am working with HelpDesk Tickets). I dont need the minimum day from one ticket and the minimum hour from another in the same timecode.
I need the minimum combination of "dd:hh:mm".
 
 
 
DateDiff ("d", {HPD_HelpDesk.Create Time},{HPD_HelpDesk.Arrival Time})
  
& " " &":"& (DateDiff ("h", {HPD_HelpDesk.Create Time},{HPD_HelpDesk.Arrival Time})
  
& " " &":"& (DateDiff ("n", {HPD_HelpDesk.Create Time}.{HPD_HelpDesk.Arrival Time})
the above equals @timeresolved
then I find
Maximum(@timeresolved)   //Works
Minimum (@timeresolved)  //Works
Average (@timeresolved)   //Doesnt work
 
thanks for your patience!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4267
Quote lockwelle Replybullet Posted: 24 Mar 2009 at 6:13am
What DBlank is saying, I think, is that the difference in minutes is all that you need, it is just a number.  It seems that you are getting stuck on the display of the number.  The min and max work, because it is picking the largest and smallest values of a string, but you can't take the average of a string.
 
DateDiff is going to return a datetime, when you want to display the number, AFTER finding the min, max, or Average; use ToText with a formatting option to display information as you want to see it.
 
After all, days and hours are just an easier way to keep track of minutes (as minutes are of seconds), and they are numbers.  Numbers are easy to transforms, strings, well they look pretty but you can't do math with them.
 
Hope this helped
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8774
Quote DBlank Replybullet Posted: 24 Mar 2009 at 6:32am
Sorry for the delayed response but you are exactly correct on my suggestion and why, lockwelle.Big%20smile

Edited by DBlank - 24 Mar 2009 at 6:32am
IP IP Logged
snapper
Newbie
Newbie


Joined: 18 Dec 2008
Online Status: Offline
Posts: 11
Quote snapper Replybullet Posted: 24 Mar 2009 at 10:33am
I got it now.
Thanks for the patience guys....I got stuck thinking one way.....
I appreciate your help.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.