Re: ACE Report Calculation Using DATETIME Type

Posted in 1997

Paul Roberts — 19 Dec 1997 18:20:58 GMT — source: Informix-list mailing list archive (1991-1998)

In article <19971219132201.IAA02221@ladder02.news.aol.com>, Ftwymgs <ftwymgs@aol.com> 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. 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)