min date - max date 8hr working hour

[#1]

Hi Experts

I am loading in from all excel files held in a folder, there are many sheets. I am looking to calculate the end to end time of an application [Process Instance] based on min(Previous start date) - Max(Current date)

The issuess I have is that there are mulitple occurances of each Process Instance and the dates could be accross many files please see sample below :

Report 1

Previous start date   Process Instance           Tray           Current date

1/1/13 9:00                    1234                         Create          1/1/13 9:30

1/1/13 10:00                   ABCD                      Review          1/1/13 10:12

1/1/13 9:30                    1234                       Review           1/1/13 11:12

1/1/13 11:12                 1234                         Post              1/1/13 12:00

1/1/13 10:12                   ABCD                      Post            1/1/13 14:00

Report 2

Previous start date   Process Instance           Tray           Current date

1/1/13 12:00                  1234                         Doc           2/1/13 9:30

1/1/13 14:00                   ABCD                    Post          1/1/13 16:00

The results we'd like is a colum with duration to date :

Process Instance        duration to date

1234                        8:30

ABCD                     6:00

Can anyone help me with this please ?

Thanks

A

[sujeetsingh]

You can derive a column in script as

=Interval(peek(Field,1) -Max(Field),'d') as DifferenceHours

[#3]

Hi sujeetsingh,

Its coming back with an error

Invalid expression

lENDERS:

can you advise if I input it incorrectly ?

Interval

(peek(Prev_Txn_Date,1) -Max(Curr_Txn_Date),'d') as DifferenceHours,

[Jason_Michaelides]

Try:

temp:

LOAD * FROM Report1...;

CONCATENATE (temp)

LOAD * FROM Report2...;

Data:

LOAD

     [Process Instance]

     ,Max(Current date) - Min(Previous start date)     AS     TotalDuration

RESIDENT temp

GROUP BY [Process Instance];

Hope this helps,

Jason

[#5]

Hi Jason,

It still doesnt seem to be working for me : Below is the script I am using and then the results Plus what I would have expected to be the results .

[SQL]

let path_Alles = '\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\Load\*.xls'; 
for each File in filelist (path_Alles)

lENDERS:
LOAD
*,
applymap('Team',Curr_Activity_Description,null()) as CurrTeam,
applymap('SubTeam',applymap('Team',Curr_Activity_Description,null()),null()) as Team,
applymap('Team',Prev_Activity_Description,null()) as PreTeam,
Filename() as File

FROM
[\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\Load\*.xls] (biff, embedded labels);

let path_Alles = '\\vs2-alpfc2\bkcCOPBUSSUP\BusinessSupport\Weekly to Monthly\NAPS\*.xls'; 
for each File in filelist (path_Alles)

concatenate (lENDERS) LOAD
*

FROM

(biff, embedded labels, header is 2 lines, filters(
Remove(Row, Pos(Top, 24)),
Remove(Row, Pos(Top, 23)),
Remove(Row, Pos(Top, 22)),
Remove(Row, Pos(Top, 21))
));

Data:

LOAD

     [Process_instance],
     Max(Curr_Txn_Date) - Min(Prev_Txn_Date)     AS     TotalDuration

RESIDENT lENDERS

GROUP BY [Process_instance];

[/SQL]

Then the results :

Process_instance	Curr_Activity_Description	Curr_Txn_Date	Prev_Activity_Description	Prev_Txn_Date	TotalDuration	DURATION SHOULD BE : Based on 24hour & including weekend
10003333	Decision	29/08/2013 16:02:35 .	Begining	29/08/2013 10:22:43 .	06:07:03	318:07:00
10003333	Decision	29/08/2013 10:22:43 .	Begining	29/08/2013 10:22:43 .	06:07:03	318:07:00
10003333	Automatic	29/08/2013 16:02:35 .	Decision	29/08/2013 16:02:35 .	06:07:03	318:07:00
10003333	Call Off	11/09/2013 16:29:45 .	Booking	11/09/2013 16:29:45 .	06:07:03	318:07:00

The result being 11/09/2013  16:29:45 minus 29/08/2013  10:22:43 based on 24 hour day and including weekends. Any guidance you may give would really be appreciated

Thanks

A

[#6]

Jason,

Im so embarrassed - you're solution works, I had the format as Time rather than Interval - however it is absed on 24 hours - is there any way to alter so its based on working day ?

Thanks

A

[Jason_Michaelides]

What do you mean, "based on working day"?  I should have wrapped the final expression in Interval() to get the hours and minutes for you - is that what you mean?

Data:

LOAD

    [Process Instance]

    ,Interval( Max(Current date) - Min(Previous start date) ) AS    TotalDuration

RESIDENT temp

GROUP BY [Process Instance];

[#8]

Hi Jason,

I mean based on 8 hours Mon to Fri rather than the 24 hour clock.

So currently , The code as used - If I have Previous 9am Mon 1st Jan and current as 10am Tues 2nd jan my total Duration is 25hrs - but I would like it as 9 hours as in 1 full 8 hour working day ( monday) and the 1 hour on Tuesday.

Using the same logic for 9am Fri 1st Jan and current as 10am Mon 2nd jan be 9 hours rather than 73 hours

Do you know if there is like in excel a workday function ?

Thanks again for your help

A

[Jason_Michaelides]

I have done this before sing a combination of NetworkDays() and DayEnd() etc.  Got quite involved and I'd need to think about it some more.

Basically you have to

1. use NetWorkDays(Min(Previous start date),Max(Current date)) to get the number of business days the interval touches.
2. Use DayEnd()-Time to get time in the first and last days.
3. Take the total days from step 1 and multiply by 8

Or something like that....I'll look in more detail if I find some time later.

Hope this helps,

Jason