select statement that calculates time ?

• Chris Root

  Valued Member

  Points: 71

  More actions

  November 16, 2005 at 2:34 pm

  #167020

  I have the query below

  select count(*), to_char(entry_time, 'YY-MON')

  from .table_act_entry ta, table_case tc

  where ta.act_entry2case = tc.objid

  and addnl_info like '%to Queue CIS - Data Cleanup%'

  and x_software_system = 'CIS'

  group by to_char(entry_time, 'YY-MON')

  order by 2;

  Which returns a desired result. I now need to attach another table which tracks whether the event is "open" or "Closed" I then need to calculate the time from opening and closing and add all the time out across the multiple cases which the above query returned. I'm sure this is confusing, but if any one can translate this with the above and below info, I'd appreciate it.

  Extra table = table_condition. Field in table_condition = condition (returns "open" or "closed")

  Time is calculated in the table - table_act_entry with the field of entry_time.

  output for the above currently looks like below

   COUNT(*) TO_CHA

  --------- ------

          1 05-JAN

          9 05-JUL

          5 05-JUN

          4 05-MAR

          3 05-MAY

          4 05-OCT

          4 05-SEP

  So in July of 2005, there was 9 cases which will have open and close times that need to be calculated then added up to get a total time. So desired output would be like below.

  Jan. 00:45:34

  Feb. 03:23:43

  Mar. 12:14:54 etc.

   

   

   

• Joe Clifford

  SSCrazy Eights

  Points: 8739

  More actions

  November 16, 2005 at 9:47 pm

  #604346

  Take a look at the datediff() function.

   

  Joe