help with interval time

[martinqlik]

Hello all,

In my application*, I calculate the execution time between several different activities. Actually, I am showing that in total time. However, I need to show that in working hours, Monday to Friday, and excluding holidays.

Does anyone knows how can I do that?

Thank y'all,

*qvw has been attached.

[johnw]

A couple not-fully-formed ideas.

1. Load a list of all weekends and holidays. Autogenerate records for working hours for all days between the start and end timestamp. Load where not exists in the list of weekends and holidays. Group by with sum() to get the total.
2. Use networkingdays() with all holidays concatenated into a variable you can use, multiply by work hours per day, then subtract out the right amount for the start and end times.

[swuehl]

Maybe have a look at

Calculate hours between two Date/Time strings

AFAIR, it demonstrates both ideas mentioned by John.

[martinqlik]

Hello, John! 

Start and End times are defined as 8:00 AM to 6:00 PM. 

Thank you for answering.

[martinqlik]

Hello, Stefan! How have you been?

I saw that post already, before I posted this issue. But, I my doubt still remaining. In this case you posted(Calculate hours between two Date/Time strings) isn't working on a case like this:

*Assuming that business hours starts at 8h00 AM to 6h00 PM

Activity Starts on 02/01/12 5h45 PM

Activity Ends  on 02/01/12 8h15 AM

The interval of them, or execution time, must be 0:30 minutes.  However, in the application it comes null. Seems like the application coverages only activities that has a Start and End in the same day.

Did you get my point?

Thank you for answering.

[swuehl]

Not sure if I understand correctly, but in your example, end timestamp is before start timestamp, which doesn't make sense to me and will not work with the posted code for sure.

[martinqlik]

Yes, you right, I meant this:

Activity Starts on 02/01/12 5h45 PM

Activity Ends  on 03/01/12 8h15 AM

Total execution time: 0:30 minutes.

Thank you,

[swuehl]

0:30 is what I get with both solutions (script based and expression based).

Double check that the timestamp format is correctly set and your values are correctly interpreted and stored as dual values.

If it's not working for you, then please post your current script.

[martinqlik]

I have been using this script:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/18/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/19/12 01:13 PM

8/25/12 17:45 PM, 8/26/12 08:30 AM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM,   7/6/12 4:19 PM  

8/1/12 09:00 AM,  8/3/12 10:00 AM

8/3/12 03:00 PM,  8/6/12 09:00 AM

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<5 and not match(Date,$(vHol))   group by ID;

drop table TMP;

[swuehl · Accepted solution]

Just entering your sample timestamps, I do get 0:30:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/18/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/20/12 01:13 PM

8/17/12 09:20 AM, 8/19/12 01:13 PM

8/25/12 17:45 PM, 8/26/12 08:30 AM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM,   7/6/12 4:19 PM 

8/1/12 09:00 AM,  8/3/12 10:00 AM

8/3/12 03:00 PM,  8/6/12 09:00 AM

1/2/12 05:45 PM, 1/3/12 08:15 AM

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<5 and not match(Date,$(vHol))   group by ID;

drop table TMP;