Re: ACE Report Calculation Using DATETIME Type
On 19 Dec 1997, Ftwymgs wrote:
> 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,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>