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

# How to calculate date difference between two steps

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.

Note 1:

 ProcessStep Date DateDiff TRP 3/12/2012 15:22 0 HLD 15/2/2013 10:53 73.81 HLD COPY 22/2/2013 10:04 6.96 TRF 25/2/2013 10:48 9.99 CAT 27/2/2013 13:59 2.13 CATL 27/2/2013 14:05 2.13 PRS 4/3/2013 14:03 4.99 HLD COPY 12/3/2013 10:28 12.84 PRSS 12/3/2013 10:46 0

Note 2 :

 No Date ProcessStep Step 643995G 03/12/2012 3:22:48 PM TRP 1 643995G 03/12/2012 3:22:50 PM TRP 1 643995G 15/02/2013 10:53:57 AM HLD 2 643995G 15/02/2013 10:54:00 AM HLD COPY 3 643995G 20/02/2013 4:38:26 PM HLD COPY 3 643995G 21/02/2013 3:27:01 PM HLD COPY 3 643995G 22/02/2013 9:38:32 AM HLD COPY 3 643995G 22/02/2013 9:39:32 AM HLD COPY 3 643995G 22/02/2013 10:04:53 AM HLD COPY 3 643995G 22/02/2013 10:04:56 AM HLD COPY 3 643995G 25/02/2013 10:48:18 AM TRF 4 643995G 25/02/2013 10:48:19 AM CAT 5 643995G 27/02/2013 1:59:44 PM CAT 5 643995G 27/02/2013 1:59:45 PM CAT 5

The output  columns as  achieved is as follows

Note 3:

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

regards,

Alvin

• ###### Re: How to calculate date difference between two steps

Something like this:

Table:

Date,

ProcessStep,

Step

FROM

DataSample.xls

(biff, embedded labels, table is Sheet1\$);

NewTable:

Step,

Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Table

Group By No, Step

Order By No, Step;

FinalTable:

Step,

// MinDate,

// MaxDate,

Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh') as Diff

Resident NewTable;

DROP Table NewTable;

• ###### Re: How to calculate date difference between two steps

Or this:

Table:

Date,

ProcessStep,

Step

FROM

DataSample.xls

(biff, embedded labels, table is Sheet1\$);

NewTable:

Step,

Min(Date) as MinDate,

Max(Date) as MaxDate

Resident Table

Group By No, Step

Order By No, Step;

FinalTable:

Step,

// MinDate,

// MaxDate,

Round(SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 1) +

SubField(Interval(RangeSum(MaxDate, -Alt(Previous(MinDate), MaxDate)), 'D.hh'), '.', 2)/24, 0.01) as Diff

Resident NewTable;

DROP Table NewTable;

• ###### Re: How to calculate date difference between two steps

Hi Sunny,

Thank You very much ... your help is much appreciated.

Regards,

Suman