Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Calculating Hours and Minutes between dates Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Topic: Calculating Hours and Minutes between dates
     Posted: 14 Apr 2015 at 7:44am
I am trying to create a report that will show me the time difference between services. In hours and minutes.
I am evaluation a Work Order
I first need the time difference between when the WO was requessted and when the first service began.
I have been able to get this with:

if {WorkOrder.LastServon} > {WorkOrder.RequestedOn} then
    DateDiff ("n", {WorkOrder.RequestedOn}, {Service.CompleteOn}) / 60.00
else
    0.00

The problem I am running into is when the WO contains multiple services. I also need the time between each service.

Any help / ideas would be greatly appreciated...
:-)
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2015 at 9:09am
you will need to explain your data set
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 14 Apr 2015 at 9:24am
SQL Database
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2015 at 9:56am
the actual content.
what are your rows and columns? What do they mean?
How is one to read the content of the data to equivocate it to the output you asked for?
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 14 Apr 2015 at 12:13pm
I apologize if I am not providing the correct information...

I have a table with a field called service.completedon This is a date/time stamp on the form mm/dd/yyyy hh:mm:ss.

In my table I have many rows, each with an entry for service.completedon I'm trying to calculates the difference in time between each row. So I want to have Crystal calculate the time difference between service.completedon in row 1, and row 2: calculate the time difference between service.completedon in row 2 and row 3; etc. etc.

Ultimately I am trying to track the time between each service and total time Work took to complete before WO was closed.
I have first grouped the report by
Work Oder Number then service key
In Report Header 1 I am displaying Work Order related information
WO #, Date/Time Requested, Service Requested etc.
In the details section of the report I am displaying the Service information
Service completed on Date/Time, Servicer, What work was completed etc..
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 4:36am
so are you trying to calculate a value using a field in one row compared to a field in another row or can you get the minutes value you need for each row and then you jsut need to sum these reults for a group?
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 15 Apr 2015 at 5:43am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 5:54am
?
IP IP Logged
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Posted: 15 Apr 2015 at 5:55am
Sample of what the report would look like:
Work Requested on 04/15/15 01:34:55 ( This is Group Header )
Service Completed on 04/15/15 01:55:12 (Details Section)
Service Completed on 04/15/15 05:25:31 (Details Section)
Service Completed on 04/16/15 10:16:11 (Details Section)
Service Completed on 04/16/15 14:57:12 (Details Section)
Service Completed on all come from same {table.field}
A separate table stores the different key values each time a new service is added to the WO.
I have figured out how to get the total time WO was open and the Time difference from when the WO was first requested to the time of the first service.
I am still struggling with the time difference between the services themselves.
I am not very technical, self taught in Crystal Reports.
Sorry not able to give a better explanation...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2015 at 6:14am
First I recommend to not fall into ta trap of trying to use how you might be displaying items in the report in a header/detail/footer as the way to get to totals. Display does not equate to actual data. What I mean is, just because you can get your report to look like a simple math equation does not mean that you altered the actual row data which is what is used for the actual calculations. Meaning, the "Work requested on" value(s) is still in every row of data even though you are only displaying it in the group header.
So if you use it in a row level formula it is calculating only based on the information in the row, not the group header value. So it is not doing calculations based on how you would do it in you mind by looking at the information displayed.
Don't get me wrong, the display is important to calulations in as much as setting up groups, sort orders, using next()/previous(), etc. but making it look like a simplified math problem does not actually make it that way.
 
With that said, I still do not understand what you want... Do you want the minutes difference between '04/15/15 01:34:55'' and "04/16/15 14:57:12" (e.g. ticket requesed date to last service completed date? or something else?
If it is something else, what is that something else based on all of the data available, not necessarily just what you are displaying.
IP IP Logged
Page  of 2 Next >>
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