Formula to calculate time based on a criteria
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22218
Printed Date: 02 May 2024 at 7:37pm
Topic: Formula to calculate time based on a criteria
Posted By: NCGOV
Subject: Formula to calculate time based on a criteria
Date Posted: 01 Feb 2017 at 4:06am
I am looking for a formula to calculate the the difference in time between two transaction types
{incilog.timestamp} = "D" (for Dispatch)
and
{incilog.timestamp} = "C" (for Last Clear)
How many HH:MM:SS were spend on the call between the Dispatch and Clear
|
Replies:
Posted By: kevlray
Date Posted: 01 Feb 2017 at 4:46am
First off you would need to do a datediff in between the two dates (in seconds) then convert it to a string to the format you want (there are formulas available out on the internet, I do not have one handy).
|
Posted By: NCGOV
Date Posted: 01 Feb 2017 at 5:37am
Thank you for your reply. The timestamps are from the same field so I am not sure how to use the DateDiff to get the difference of time in the same field.
There is a time stamp for the dispatch and a time stamp for the clear. If it were different fields, I can figure it out but there is the variable and the same filed.
DateDiff ("s",{incilog.timestamp}, {incilog.timestamp}) will not work unless I add the variable of the transaction type.... dispatched and cleared. I just cannot figure out how to do it.
|
Posted By: kevlray
Date Posted: 01 Feb 2017 at 6:51am
|
Then I am assuming that they are different records and that you are grouping the report in some way. If so, then you will need to store the first (or last date) in a global (I have better luck with shared) variable at the beginning of the grouping (Group Header) and then do the calculation in the Group Footer using the value from the global (or shared variable).
|
Posted By: DBlank
Date Posted: 01 Feb 2017 at 7:01am
|
you can also join the table to itself on the PK and only pull the row with "D" in one version and "C" in the other and use that resulting data set to get the values you need
|
Posted By: NCGOV
Date Posted: 01 Feb 2017 at 9:21am
Hi! No, there are no groups.
There is a "TimeStamp" Field that puts the time of each transaction.
Since the data is housed in one field, I am not sure how to calculate the time between the transaction of Dispatched and Cleared.
|
Posted By: kevlray
Date Posted: 01 Feb 2017 at 12:11pm
|
DBlank's solution will work real good if you know how to write a command (i.e., TSQL query). Otherwise you will need to group your data.
|
|