I am in need of a formula that will loop through records in a table and calculate the time between two dates. The fields include a ticket number{NUMBER_SC}, the status of the ticket status {TYPE}(could be any of the following: open, assignment, reasignment, suspended, unsuspended, resolved, closed and a few others) and the date/time field{DATESTAMP}. One ticket number will have (many) records. Each time a user changes the ticket status {TYPE} a new entry is made into the table in cronological order{DATESTAMP}. I need to loop through each ticket number and find where the {TYPE} = "Suspend" and then find the next record where the {TYPE} = "Unsuspend". I will then need to find the difference between the two dates in seconds. One more thing, the same ticket can be suspended/unsuspended more than once. In that case I need to find the TOTAL seconds for all of the suspend/unsuspends. I am using CR8.0. I hope I have provided enough info. If it would help to see a snapshot of the table I could send it if needed.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Loop/If formula needed from an Expert!
- Thread starter SchuhTL
- Start date
This isn't a LOOP formula. This is a fairly complex formula using Crystal variables.
Are you familiar with Crystal syntax for variables?
How often do you want to see the total seconds?
What if the last record is a suspend?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Are you familiar with Crystal syntax for variables?
How often do you want to see the total seconds?
What if the last record is a suspend?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #3
Ken, thanks for your response! I am more fimiliar with basic syntax, but I am willing to use anything at this point. The tickets in this particular table have all been resolved so for every ticket that has been suspended there is a unsuspended. I would like to see one total per ticket. If a ticket has been suspended and unsuspended three times, I would only like to see one total for that ticket.
Since they are always in pairs, here is a simpler approach:
Select for the report only records of types suspended and Unsuspended. Group by Ticket and sort by time stamp.
Now write a formula that says
If Type = Unsuspend
then Datestamp - Previous (DateStamp)
else 0
Convert this from Days to seconds, and use the 3 formula technique in faq767-995 to sum this formula.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Select for the report only records of types suspended and Unsuspended. Group by Ticket and sort by time stamp.
Now write a formula that says
If Type = Unsuspend
then Datestamp - Previous (DateStamp)
else 0
Convert this from Days to seconds, and use the 3 formula technique in faq767-995 to sum this formula.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #5
See faq149-243 for formulas for finding business days, and also business hours, between two date time values. Use your Current and previous values for input into these formulas. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #7
You replace the fields in the sample formula with your field names:
4th line: TimeValue ({TimeStamp})
5th line: TimeValue (Previous ( {TimeStamp}))
You also have to use the Business Days formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
4th line: TimeValue ({TimeStamp})
5th line: TimeValue (Previous ( {TimeStamp}))
You also have to use the Business Days formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #9
Ken, when I insert the formula it is giving me neg Business Hour and it is also giving me the difference between each record. For Example:
Suspend
Unsuspend -4.25
Suspend -25.26
Unsuspend -34.62
Suspend -24.02
Unsuspend -57.79
The Unsuspend time is the correct number of hours I am looking for(It would be nice to have a positive #). I can't think of any way to sum just the Unsuspend's. Any thoughts?
Suspend
Unsuspend -4.25
Suspend -25.26
Unsuspend -34.62
Suspend -24.02
Unsuspend -57.79
The Unsuspend time is the correct number of hours I am looking for(It would be nice to have a positive #). I can't think of any way to sum just the Unsuspend's. Any thoughts?
They are negative because I had you put the Previous as the End Time, it should be the other way around.
In your running total use an If-Then in the assignment to only accumulate the unsuspend records. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
In your running total use an If-Then in the assignment to only accumulate the unsuspend records. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #11
Ken,
I have changed the Start Time in both of your formula's(Work days & Business hours ) to read Previous({ACTIVITY.DATESTAMP})). I have changed the End Time to read ({ACTIVITY.DATESTAMP}). Here is the results I am getting:
TYPE DATESTAMP FORMULA
Suspend 02/19/2002 9:34:22 AM
Unsuspend 02/19/2002 1:49:36 PM -19.75
Suspend 02/19/2002 2:16:56 PM -23.54
Unsuspend 02/20/2002 3:54:24 PM -13.38
Suspend 02/20/2002 3:55:37 PM -23.98
Unsuspend 02/26/2002 1:42:57 PM 9.79
Any thoughts?
I have changed the Start Time in both of your formula's(Work days & Business hours ) to read Previous({ACTIVITY.DATESTAMP})). I have changed the End Time to read ({ACTIVITY.DATESTAMP}). Here is the results I am getting:
TYPE DATESTAMP FORMULA
Suspend 02/19/2002 9:34:22 AM
Unsuspend 02/19/2002 1:49:36 PM -19.75
Suspend 02/19/2002 2:16:56 PM -23.54
Unsuspend 02/20/2002 3:54:24 PM -13.38
Suspend 02/20/2002 3:55:37 PM -23.98
Unsuspend 02/26/2002 1:42:57 PM 9.79
Any thoughts?
Post your exact business hours formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Thread starter
- #13
WhilePrintingRecords;
NumberVar Days := {@WorkDays}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"
; // The start your work day
TimeVar SetEnd := TimeValue("17:00" // The end your work day
TimeVar StartTime := TimeValue(Previous({ACTIVITY.DATESTAMP}));// The data field that holds your Start Time
TimeVar EndTime := TimeValue({ACTIVITY.DATESTAMP}); // The data field that holds your End Time
//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM. Of course, this won't work if your workday is over 12 hours.
//If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
//If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;
Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)
NumberVar Days := {@WorkDays}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"
; // The start your work day TimeVar SetEnd := TimeValue("17:00"
; // The end your work day TimeVar StartTime := TimeValue(Previous({ACTIVITY.DATESTAMP}));// The data field that holds your Start Time
TimeVar EndTime := TimeValue({ACTIVITY.DATESTAMP}); // The data field that holds your End Time
//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM. Of course, this won't work if your workday is over 12 hours.
//If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
//If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;
Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)
It seems OK, so I am puzzled. Is {@WorkDays} giving the correct number?
To troubleshoot, I would create an extra formula on the detail band and declare one variable in it to check that it is returning the correct value. Then replace it with the next variable, etc. See if each variable used in this formula is what you expect it to be. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
To troubleshoot, I would create an extra formula on the detail band and declare one variable in it to check that it is returning the correct value. Then replace it with the next variable, etc. See if each variable used in this formula is what you expect it to be. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB
tek@kenhamady.com
- Status
Similar threads
- Locked
- Question