ACE Report Calculation Using DATETIME Type
Author Message
ACE Report Calculation Using DATETIME Type

I am trying to write an ACE Report that does calculations on columns of type
DATETIME YEAR TO MINUTE and I am really stuck....

What I have is two fields BEGIN_TIME and CLOSE_TIME of type DATETIME. These
fields represent the time a trouble ticket was opened and closed. I want
to produce a report that calculates the total number of times a ticket was
closed
within 15 minutes, within 4 hours and over 24 hours.

I can't figure out how to calculate the difference in minutes of these two
fields.

Can anyone offer any help?

Happy Holidays.......

I

Wed, 18 Jun 1902 08:00:00 GMT
ACE Report Calculation Using DATETIME Type

Quote:

> I am trying to write an ACE Report that does calculations on columns of type
> DATETIME YEAR TO MINUTE and I am really stuck....

> What I have is two fields BEGIN_TIME and CLOSE_TIME of type DATETIME. These
> fields represent the time a trouble ticket was opened and closed. I want
> to produce a report that calculates the total number of times a ticket was
> closed
> within 15 minutes, within 4 hours and over 24 hours.

> I can't figure out how to calculate the difference in minutes of these two
> fields.

> Can anyone offer any help?

The difference between two datetimes is an interval, so I think you'll
need an interval variable
My ace is really rusty, so below is 4GL syntax you'll have to translate,
although I'm pretty sure all concepts will apply to ace:

DEFINE tr_duration INTERVAL HOUR(4) TO MINUTE

...
LET tr_duration = CLOSE_TIME - BEGIN_TIME

IF tr_duration > INTERVAL( 24:00 HOUR TO MINUTE ) THEN
...

Check my syntax, but that's the basic idea.

--
//////////////// =======================================================
//////////    // Dennis J. Pimple           Informix Software, Inc.
//////    /  /// Principal Consultant       6300 S Syracuse Way Ste 205

////    // /////
///    // ////// recept: 303-850-0210
//    // /////// direct: 303-740-5611       Opinions expressed are mine,
/    ///////////    fax: 303-779-4025       and do not necessarily
////////////////   http://www.informix.com  reflect those of my employer

Wed, 18 Jun 1902 08:00:00 GMT
ACE Report Calculation Using DATETIME Type

Quote:
> I am trying to write an ACE Report that does calculations on columns of type
> DATETIME YEAR TO MINUTE and I am really stuck....

> What I have is two fields BEGIN_TIME and CLOSE_TIME of type DATETIME.
> These fields represent the time a trouble ticket was opened and closed.
> I want to produce a report that calculates the total number of times a
> ticket was closed within 15 minutes, within 4 hours and over 24 hours.

> I can't figure out how to calculate the difference in minutes of these two
> fields.

> Can anyone offer any help?

This is similar to a question I dealt with in the I4GL Tips, Tricks and
Optimization presentation at IWUC 97.

===========================================================================

I have a table with a DATETIME HOUR TO MINUTE column.
How do I count the number of rows in each 5 minute interval?

Calculate the number of minutes past midnight for each time.
*   Use stored procedure: dt_minutes
*   SELECT 5 * TRUNC(dt_minutes(dt_hm)/5), COUNT(*)
FROM SomeTable
GROUP BY 1;

-- Untested transcription of tested code!
*   CREATE PROCEDURE dt_minutes(d DATETIME HOUR TO MINUTE)
RETURNING INTEGER;
DEFINE i INTERVAL MINUTE(4) TO MINUTE;
DEFINE s CHAR(5);
LET i = d - DATETIME(0:0) HOUR TO MINUTE;
LET s = i;
RETURN s;
END PROCEDURE;

Now suppose I want to see the start time of each interval as a time value?
*   SELECT  DATETIME(0:0) HOUR TO MINUTE +
(5 * TRUNC(dt_minutes(dt_hm)/5)) UNITS MINUTE AS start_time,
COUNT(*)
FROM SomeTable
GROUP BY 1;

===========================================================================

Adapting to your situation, you may be dealing with longer intervals than
just 24 hours, so you will need to change the the stored procedure to return
the integer number of minutes between two datetime values:

-- Untested code
CREATE PROCEDURE dt_diff_minutes(d1 DATETIME YEAR TO MINUTE, d2 DATETIME YEAR TO MINUTE)
RETURNING INTEGER;
DEFINE i INTERVAL MINUTE(9) TO MINUTE;
DEFINE s CHAR(10);
LET i = d1 - d2;
LET s = i;
RETURN s;
END PROCEDURE;

You can then use this in the SELECT to determine how many minutes it took to
close the case:

SELECT  ...
dt_diff_minutes(close_time, begin_time) minutes_to_close,
...

And you can use this in your group or overall aggregates:

PRINT GROUP COUNT(*) WHERE minutes_to_close < 15;   -- < 15 mins
PRINT GROUP COUNT(*) WHERE minutes_to_close < 240;  -- <  4 hrs
PRINT GROUP COUNT(*) WHERE minutes_to_close > 1440; -- > 24 hrs

Yours,

Wed, 18 Jun 1902 08:00:00 GMT
ACE Report Calculation Using DATETIME Type

Quote:

>I am trying to write an ACE Report that does calculations on columns of type
>DATETIME YEAR TO MINUTE and I am really stuck....

>What I have is two fields BEGIN_TIME and CLOSE_TIME of type DATETIME. These
>fields represent the time a trouble ticket was opened and closed. I want
>to produce a report that calculates the total number of times a ticket was
>closed within 15 minutes, within 4 hours and over 24 hours.

This seems to work for me...

database your_db

select "1.  within 15 minutes" speed, count(*) cnt
from YOUR_TABLE
where CLOSE_TIME - BEGIN_TIME       < "        0 00:15"
union
select "2.  15 min - 4 hours ", count(*)
from YOUR_TABLE
where CLOSE_TIME - BEGIN_TIME between "        0 00:26" and "        0 04:00"
union
select "3.  4 - 24 hours     ", count(*)
from YOUR_TABLE
where CLOSE_TIME - BEGIN_TIME between "        0 04:01" and "        1 00:00"
union
select "4.  over 24 hours    ", count(*)
from YOUR_TABLE
where CLOSE_TIME - BEGIN_TIME                             > "        1 00:01"
end

format

on every row
print speed, " ", cnt

end

... cheers,

Paul   (not a spokesman)

Wed, 18 Jun 1902 08:00:00 GMT
ACE Report Calculation Using DATETIME Type

Ftwymgs> I am trying to write an ACE Report that does calculations
Ftwymgs> on columns of type DATETIME YEAR TO MINUTE and I am
Ftwymgs> really stuck....

Ftwymgs> What I have is two fields BEGIN_TIME and CLOSE_TIME of
Ftwymgs> type DATETIME. These fields represent the time a trouble
Ftwymgs> ticket was opened and closed. I want to produce a report
Ftwymgs> that calculates the total number of times a ticket was
Ftwymgs> closed within 15 minutes, within 4 hours and over 24
Ftwymgs> hours.

Ftwymgs> I can't figure out how to calculate the difference in
Ftwymgs> minutes of these two fields.

Ftwymgs> Can anyone offer any help?

Ftwymgs> Happy Holidays.......

Ftwymgs> I

cast your result to ::interval minute to minute.

--
Thomas Tatum

919-489-5662 x32
919-602-6521

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages