Calculation of time difference

[balrammahato204]

Hello Guys, please help me i got stuck in one problem:-

I want to calculate the Total time difference from Assigned status to last In Progress status.

But there is a condition if there is a any Pending status in the column and then after just there is a first in progress status, time difference should be exclude. Eventually, what would be the final time difference.

Hers is a scenario:

ID NO	Date	Status
ID001	3/20/2015  6:43:25 AM	Assigned
ID001	3/20/2015  6:46:56 AM	In Progress
ID001	3/20/2015  6:52:13 AM	Pending
ID001	3/23/2015  2:42:26 AM	In progress
ID001	3/23/2015  2:42:29 AM	In progress
ID001	3/24/2015  2:15:23 AM	Pending
ID001	3/27/2015  9:15:23 AM	Pending
ID001	3/28/2015  9:15:23 AM	In Progress

[swuehl]

Not sure if I fully understood your condition. If there is a Pending Status per ID before a In Progress Status, you want the interval not being calculated.

INPUT:

LOAD * INLINE

[

ID NO,Date,Status

ID001,3/20/2015 6:43:25 AM,Assigned

ID001,3/20/2015 6:46:56 AM,In Progress

ID001,3/20/2015 6:52:13 AM,Pending

ID001,3/23/2015 2:42:26 AM,In Progress

ID001,3/23/2015 2:42:29 AM,In Progress

ID001,3/24/2015 2:15:23 AM,Pending

ID001,3/27/2015 9:15:23 AM,Pending

ID001,3/28/2015 9:15:23 AM,In Progress

ID002,3/20/2015 6:43:25 AM,Assigned

ID002,3/20/2015 6:52:13 AM,Pending

ID002,3/23/2015 2:42:26 AM,In Progress

ID002,3/23/2015 2:42:29 AM,In Progress

ID002,3/24/2015 2:15:23 AM,Pending

ID002,3/27/2015 9:15:23 AM,Pending

ID002,3/28/2015 9:15:23 AM,In Progress

];

ID001 will be calculated, ID002, not.

You can check this condition within a script calculation, grouping your data per ID NO and aggregating the Date values:

INTERVALS:

LOAD *, if(IsNull(Interval),'Omitted') as Flag;

LOAD [ID NO],

  Interval(if(Min(if(Status='Pending', Date)) > Min(if(Status = 'In Progress',Date)), Max(if(Status='In Progress', Date)) - Min(if(Status = 'Assigned',Date)) )) as Interval

RESIDENT INPUT

GROUP BY [ID NO];

edit: And please try to avoid multiple postings with the same request. It's hard to follow what's been discussed in the other threads.

[balrammahato204]

thanks swuehl for your solution.

But in this calculation i also want to include some more condition-

.Exclude sat sun time and also exclude the holiday

.work hour calculation in between 08:00 AM to 6:00 PM.

How can i put your given logic into networkdays calculation.

[swuehl]

You can do it like I described here:

Calculate hours between two Date/Time strings

Sample attached.

[balrammahato204]

hi swuehl

As per your solution pending time is not excluding from the workingintervel. so please could you look it once. for the calculation i have just taken the simple values in ID001 and trace your script. Then i found

TotalIntervel = 07:00:00

workingintervel=7:00:00

LOAD

* INLINE

[
 

ID NO,Date,Status
ID001,3/20/2015 8:00:00 AM,Assigned
ID001,3/20/2015 9:00:00 AM,In Progress
ID001,3/20/2015 10:00:00 AM,Pending
ID001,3/20/2015 11:00:00 AM,In Progress
ID001,3/20/2015 12:00:00 PM,In Progress
ID001,3/20/2015 01:00:00 PM,Pending
ID001,3/20/2015 02:00:00 PM,Pending
ID001,3/20/2015 03:00:00 PM,In Progress

ID002,3/20/2015 6:43:25 AM,Assigned
ID002,3/20/2015 6:52:13 AM,Pending
ID002,3/23/2015 2:42:26 AM,In Progress
ID002,3/23/2015 2:42:29 AM,In Progress
ID002,3/24/2015 2:15:23 AM,Pending
ID002,3/27/2015 9:15:23 AM,Pending
ID002,3/28/2015 9:15:23 AM,In Progress
]

Regards

Balram

[swuehl]

Looks ok to me, ID001 all happens within the same date, same working hour interval 8AM to 6 PM.

So no issue if TotalInterval equals WorkingInterval.

[balrammahato204]

actuaaly i want like this

3/20/2015 10:00:00 AM,Pending

3/20/2015 11:00:00 AM,In Progress

ID001,3/20/2015 01:00:00 PM,Pending

ID001,3/20/2015 02:00:00 PM,Pending

ID001,3/20/2015 03:00:00 PM,In Progress

if this situation will come then i want to exclude the time it means from the first condition 1 hour and second condition 2 hour. Total hour is 3 hr. I want also exclude these 3 hour from the WorkingInterval.

[pratap6699]

by using Date(maxdatefield)-Date(mindatefield)-->it gives dates differences

interval(hightime)-interval(lowtime)-->it gives time differences with format

[swuehl]

Please have a look at attached sample file.

[balrammahato204]

Hi

Swuehl,

now i am getting the perfect output. Now i have required one more help if in this calculation i am adding one more column as a "Priority" and when priority will change previous calculation will be zero and new calculation will start from the last priority change timestamp.

LOAD
* INLINE
[


ID NO,Date,Status, Priority
ID001,3/20/2015 8:00:00 AM,Assigned,P1
ID001,3/20/2015 9:00:00 AM,In Progress,P1
ID001,3/20/2015 10:00:00 AM,Pending,P1,
ID001,3/20/2015 11:00:00 AM,In Progress,P2
ID001,3/20/2015 12:00:00 PM,In Progress,P2
ID001,3/20/2015 01:00:00 PM,Pending,P2
ID001,3/20/2015 02:00:00 PM,Pending,P2
ID001,3/20/2015 03:00:00 PM,In Progress,P3
ID001,3/20/2015 04:00:00 PM,In Progress,P3
ID001,3/20/2015 05:00:00 PM,In Pending,P3
ID001,3/20/2015 06:00:00 PM,In Progress,P3

ID002,3/20/2015 6:43:25 AM,Assigned
ID002,3/20/2015 6:52:13 AM,Pending
ID002,3/23/2015 2:42:26 AM,In Progress
ID002,3/23/2015 2:42:29 AM,In Progress
ID002,3/24/2015 2:15:23 AM,Pending
ID002,3/27/2015 9:15:23 AM,Pending
ID002,3/28/2015 9:15:23 AM,In Progress
]

In this example calculation will start from starting time of P3 and End time of P3 and

It means my total ''workinginterval  2Hour'' for ID001.

Regards

Balram