Trying to determine time intervals between 2 records.

[qu1ncy]

Hello,

We’re using CR8.5 with an Oracle 9.1 DB. I’m using an Oracle view to pull a dataset which lists records based on opened & closed actions. Each action has a date & time associated.
1.) I would like to determine the time interval between the open & close actions. The date will be the same for both actions but the time will differ by as much as a few minutes or as little as a few seconds.
2.) I’m grouping on Subscriber Name and I don’t want to display any records that don’t have a “Closed” action. I don’t want to display Subscriber name 2.

The data looks like this:

Subscriber Name 1
Action Trans_Date Trans_Time Field_ID Queue

Open 2007-09-01 1:00:34 PM G Analyst
Closed 2007-09-01 1:01:03 PM SELECT FROM LIST Analyst

Subscriber Name 2
Action Trans_Date Trans_Time Field_ID Queue

Open 2007-09-01 12:56:37 PM G Analyst


Still learning,
Thanks,
Q

 

[lbass]

Create a formula like this:

//{@Closed}:
if {table.status} = "Closed" then 1

Then go to report->selection formula->GROUP and enter:

sum({@Closed},{table.subscribername}) > 0

To get the time difference use:

if {table.subscribername} = previous({table.subscribername}) then
datediff("s",previous({table.datetime}),{table.datetime})

This will give you the time difference in seconds.

-LB

 

[qu1ncy]

Thanks lbass for your prompt response.

Adding the @Closed formula and adjustment to the group selection formula worked nicely. As does the time difference calculation.
I've called your time difference formula @Time_Interval_Sec. I’ve pulled @Time_Interval_Sec into another formula called @Time_Interval_Min and used it as follows: {@Time_Interval_Secs}/60 &" Minutes".

@Time_Interval_Min was placed in the details section. I’m seeing a value of 0.0 minutes for the “Open” record, and the correct time interval in the “Closed” record.

Open 2007-09-01 1:00:34 PM G 0.00 minutes
Closed 2007-09-01 1:01:03 PM LIST 0.50 minutes

How can I suppress the 0.0 values?

Lastly - I would like to filter out any groups that have an Open – Closed time interval greater than 5 minutes.

Thanks,
Q

 

[lbass]

You should be able to right click on {@TimeIntervalSecs} and->format field->numbers->customize->suppress if zero. Add the "minutes" in a text box.

By filter out do you mean prevent from display or do you mean you want to show those with an interval greater than 5 minutes? I think you would have to use section suppression, so go into the section expert->details->suppress->x+2 and enter:

(
{table.subscribername} = previous({table.subscribername}) and
datediff("s",previous({table.datetime}),{table.datetime})/60 <= 5
) or
(
{table.subscribername} = next({table.subscribername}) and
datediff("s",{table.datetime},next({table.datetime}))/60 <= 5
)

You might have to make the signs > if that was what you meant.

-LB

 

[qu1ncy]

LBass
Thank you very much for your assistance with this. Your instructions as followed worked beautifully.
Your expertise is appreciated.
Q