Archives of the TeradataForum

Message Posted: Wed, 24 Feb 2016 @ 20:03:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello Experts,

I have a requirement to calculate time spent on ticket each time it moved from Need-info state to any other state. Below is the sample data.

     No    Ticket_no   State       update_time
      1    1076551-1   open        2015-04-29 02:53:52.840000
      2    1076551-1   open        2015-05-06 12:45:38.950000
      3    1076551-1   open        2015-06-12 09:33:01.860000
      4    1076551-1   open        2015-06-12 16:32:28.430000
      5    1076551-1   need-info   2015-06-18 12:32:45.830000
      6    1076551-1   open        2015-06-19 17:32:15.910000
      7    1076551-1   need-info   2015-06-23 14:33:08.270000
      8    1076551-1   need-info   2015-07-03 02:32:05.050000
      9    1076551-1   need-info   2015-09-11 11:57:44.360000
     10    1076551-1   need-info   2015-09-15 16:14:59.520000
     11    1076551-1   resolved    2015-12-01 20:25:58.990000
     12    1076551-1   open        2015-12-10 00:03:44.440000
     13    1076551-1   closed      2016-01-19 03:58:51.090000

Here, I need to pick records 6 and 5 and then 11 and 10 then it has get subtracted and summed accordingly.

Something like below

     SELECT (2015-06-19 17:32:15.910000 - 2015-06-18 12:32:45.830000) DAY(4) TO SECOND
          + (2015-12-01 20:25:58.990000 - 2015-09-15 16:14:59.520000) DAY(4) TO SECOND
          from DB.Table;

Is this feasible to achieve via SQL? Any pointers would be greatly appreciated!

Thanks,

John

	<Prev	Next>		<<First	<Prev	Next>	Last>>