0 Replies Latest reply: Sep 14, 2016 5:52 AM by Sumanth C RSS

    Calculate Age based on next records data

    Sumanth C

      Hi,

       

      I have data as below. A Ticket flows into different Departments,and have to calculate the Age of Ticket in each Department.(Cumulative)

       

      A department can be reassigned after moving from a different one.In that case we need calculate cumulatively.

       

      IDTicketDateDepartment
      334250012016-08-09

      A

      334370012016-08-09A
      334390012016-08-09A
      335570012016-08-29G
      335630012016-08-29G
      337950012016-08-31G
      338020012016-09-02A
      350940012016-09-02A
      350970012016-09-03A

       

      So here, the output should be as

       

      For Department A :

      ID - 33425 to ID - 33439

      (2016- 08- 29 ) to  (2016-08-09)  = Difference is 0

       

      +

      ID -  33802 to ID- 35097

      (2016-09-02) to (2016-09-03) = Difference is 1

       

      So Total Age in Department A is 0+1 = 1.

       

      Where as Calculation for Dept G should be as below.

      Ticket went to Dept G on 2016-08-29 and moved to Dept A only on 2016-09-02, which mean Ticket was being handled by Dept G until 2016- 09- 02.

      So here the Age should be calculated from

      2016 -08-29 (ID , 33557) to 2016-09-02 (ID,33802).  - 4

       

      Request you to help on this.

       

      Thanks,