3 Replies Latest reply: Apr 21, 2016 7:43 PM by alvinford ford RSS

    How to calculate date difference between two steps

    alvinford ford

      Hi All,

       

      I have the following data referring Note 2. Would like to achieve the output referring to Note 1. Would like to calculate the Date difference between two steps and achieve the DateDiff column. Kindly refer the Note 3 for reference how output is achieved.

       

      Please refer the attachment for Data sample.

       

      Thank You in Advance..

       

       

      Note 1:

         

      ProcessStepDateDateDiff
      TRP3/12/2012 15:220
      HLD15/2/2013 10:5373.81
      HLD COPY22/2/2013 10:046.96
      TRF25/2/2013 10:489.99
      CAT27/2/2013 13:592.13
      CATL27/2/2013 14:052.13
      PRS4/3/2013 14:034.99
      HLD COPY12/3/2013 10:2812.84
      PRSS12/3/2013 10:460

       

       

       

      Note 2 :

          

      NoDateProcessStepStep
      643995G03/12/2012 3:22:48 PMTRP1
      643995G03/12/2012 3:22:50 PMTRP1
      643995G15/02/2013 10:53:57 AMHLD2
      643995G15/02/2013 10:54:00 AMHLD COPY3
      643995G20/02/2013 4:38:26 PMHLD COPY3
      643995G21/02/2013 3:27:01 PMHLD COPY3
      643995G22/02/2013 9:38:32 AMHLD COPY3
      643995G22/02/2013 9:39:32 AMHLD COPY3
      643995G22/02/2013 10:04:53 AMHLD COPY3
      643995G22/02/2013 10:04:56 AMHLD COPY3
      643995G25/02/2013 10:48:18 AMTRF4
      643995G25/02/2013 10:48:19 AMCAT5
      643995G27/02/2013 1:59:44 PMCAT5
      643995G27/02/2013 1:59:45 PMCAT5

       

      The output  columns as  achieved is as follows

       

      Note 3: 

      DateDiff is achieved as Process is achieved as Date is achieved
      Min of Step 1ProcessStep of Step 1Min Date of Step 1
      Max(Step 2)-  Min( Step1)ProcessStep of Step 2Max Date of Step 2
      Max(Step3)-  Min( Step2)ProcessStep of Step 3Max Date of Step 3
      Max(Step4)-  Min( Step3)ProcessStep of Step 4Max Date of Step 4
      Max(Step5)-  Min( Step4)ProcessStep of Step 5Max Date of Step 5
      Max(Step6)-  Min( Step5)ProcessStep of Step 6Max Date of Step 6
      Max(Step7)-  Min( Step6)ProcessStep of Step 7Max Date of Step 7
      Max(Step8)-  Min( Step7)ProcessStep of Step 8Max Date of Step 8
      Max(Step9)-  Min( Step8)ProcessStep of Step 9Max Date of Step 9

       

      regards,

      Alvin