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
 
 [ 5 post ] 

 Relevant Pages 

1. Datetime data type in ACE reports

2. Informix SE on Solaris, using ACE report writer...

3. Selecting printer in ACE reports using prompt

4. field type Calculations versus autoentry via calculation

5. DateTime data type for Crystal reports and SQL database

6. Date/Time Calculations in ACE

7. Converting to datetime data type using subselect

8. HELP!! with usage of datetime in ace

9. conversion from DateTime type to float type

10. cast from datetime type to smalldatetime type

11. Datetime calculations

12. DateTime calculation


 
Powered by phpBB® Forum Software