I have a request from a department to count orders offered (available to their division) for over 3hrs Mon-Fri between the hours on 7 and 17:00. Can webfocus count only those hours?

The format is HYYMDs. I have a starting date/time and an ending date/time, but not sure how to count only those hours.

Thanks!

This message has been edited. Last edited by: Kerry,

To limit retrieval to rows where DateTime fields DT1 and DT2 differ by more than 3:00:00, you can use

WHERE HDIFF(DT2, DT1, 'SECOND', 'D10') GT (3*60*60);

I would suggest...

DEFINE FILE ORDERS_TABLE
 DateTime/HYYMDs = MODIFY_DTM ;
 Time_MilliSec/D12.2 = HTIME(8, DateTime, 'D12.2');
 From_MilliSec/D12.2 = HTIME(8, DT(07:00:00.000), 'D12.2');
 To_MilliSec  /D12.2 = HTIME(8, DT(17:00:00.000), 'D12.2');
 Count_Orders/I9 = IF Time_MilliSec GE From_MilliSec AND Time_MilliSec LE To_MilliSec THEN 1 ELSE 0 ;
END

TABLE FILE ORDERS_TABLE
 PRINT 
       DateTime
       Time_MilliSec
       From_MilliSec
       To_MilliSec
       Count_Orders

WHERE  HPART(DateTime, 'dw', 'I1') GE 2
WHERE  HPART(DateTime, 'dw', 'I1') LE 6
END

I can calculate the difference in time and what day of the week, but how do I only count the hours M-F between 7am and 5pm?

IE. An order is offered Monday at 3pm. It is covered/dispatched Wednesday at 9am. I want to calculate the hours for Monday (3 to 5pm), Tuesday (7am - 5pm), and Wednesday (7 - 9am).

Also, if an order is offered Friday and is accepted, say, Tuesday one week later, I don't want to count the 2 weekends in that time frame.

You could use the HPART function in a DEFINE to extract the Hour from the date-time fields and then filter on this field...

Think McGuyver.

Create a table keyed to date, with a record for each business-day giving its start- and end-of-business-day as DateTime values.

Calculate the processing start- and end-dates of the order; to join to the date table and pull all business dates over the course of the order.

The join allows you to determine the 'start-of-processing' and 'end-of-processing' DateTime values of the order for each date that the interval overlaps (defined using MIN and MAX, or equivalent IF THEN ELSE logic), and the processing time in minutes for each business day based on those two calculated values. Sum that over all the overlapped business days, and you have the order's processing-time metric value.

This message has been edited. Last edited by: j.gross,

Would something like this work?

-SET &calendar1 = '2011/01/04 16:12:12.00000';
-SET &calendar2 = '2011/01/11 12:43:12.00000';

SET BUSDAYS=_MTWTF_
DEFINE FUNCTION DELIVERY(startDate/HYYMDs,endDate/HYYMDs)
wdays/I6 = DATEDIF(HDATE(startDate,'YYMD'),HDATE(endDate, 'YYMD'), 'BD');
adays/I6 = HDIFF(endDate,startDate,'DAY', 'D6.0');
endMins/i8 = HDIFF(endDate,HSETPT(HSETPT(HSETPT(endDate,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',7,8,'HYYMDI'),'MINUTE','D8.0');
startMins/i8 = HDIFF(HSETPT(HSETPT(HSETPT(startDate,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',17,8,'HYYMDI'),startDate,'MINUTE', 'D8.0');
DELIVERY/D8.1 = ((wdays-(adays-wdays-1))*10)+((startMins+endMins)/60);
END
DEFINE FILE FN
startDate/HYYMDs = HINPUT(25,'&calendar1',8,'HYYMDs');
endDate/HYYMDs = HINPUT(25,'&calendar2',8,'HYYMDs');
time/D8.1 = DELIVERY(startDate,endDate);
END
TABLE FILE FN
PRINT time
END

There are a few caveats attached to Alan's closed-form approach, depending on the application's requirements. It will not be applicable if ...

... business hours may vary (e.g., early close on eve of a holiday)

... the order arrival time may occur on a non-business day (DATEDIF with 'BD' effectively adjusts its date argument values forward to a business date; and the portion of the arrival date to be counted would deserve to be zero)

I don't think I'm doing something right Alan B. The time doesn't look correct to me.

DEFINE FUNCTION DELIVERY(TIMEIN/HYYMDS,TIMEOUT2/HYYMDS)
WDAYS/I6 = DATEDIF(HDATE(TIMEIN,'YYMD'),HDATE(TIMEOUT2, 'YYMD'), 'BD');
ADAYS/I6 = HDIFF(TIMEOUT2,TIMEIN,'DAY', 'D6.0');
ENDMINS/I8 = HDIFF(TIMEOUT2,HSETPT(HSETPT(HSETPT(TIMEOUT2,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',7,8,'HYYMDI'),'MINUTE','D8.0');
STARTMINS/I8 = HDIFF(HSETPT(HSETPT(HSETPT(TIMEIN,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',17,8,'HYYMDI'),TIMEIN,'MINUTE', 'D8.0');
DELIVERY/D8.1 = ((WDAYS-(ADAYS-WDAYS-1))*10)+((STARTMINS+ENDMINS)/60);
END
DEFINE FILE ALLDIVS
TIME/D8.1 = DELIVERY(TIMEIN,TIMEOUT2);
END

Output:
TIMEIN TIMEOUT2 TIME
2011/01/03 08:16:12 2011/01/03 18:41:55 30.4
2011/01/03 08:17:13 2011/01/03 20:48:25 32.5
2011/01/03 08:18:06 2011/01/03 17:06:25 28.8
2011/01/03 08:19:35 2011/01/03 19:01:24 30.7


I also tried another way, and the time was off by a few hours.
COMPUTE YRPORT/WRMTDYY=HDATE(TIMEIN, 'YYMD');
COMPUTE YREND/WRMTDYY=HDATE(TIMEOUT2, 'YYMD');
COMPUTE WEEKDAYS/I5=DATEDIF(YRPORT, YREND, 'BD');
COMPUTE DAYS/I5=DATEDIF(YRPORT,YREND, 'D');
COMPUTE DIFF/D12.2=HDIFF(TIMEOUT2, TIMEIN, 'HOUR', DIFF);
COMPUTE RESULT/D12.2=DIFF - (WEEKDAYS * 14) - ((DAYS - WEEKDAYS) * 24);

When timein and timeout are on the same date (as in the four examples you give), the defined function overcounts in several ways.

You need, roughly speaking:

firstdayhours= if timein is on a business day and time part of timein < 1700
then elapsed hours from max(timein, 0700 on the date of timein) to min(timeout, 1700 on the date of timein)
else 0 ;

lastdayhours = if timein and timeout are on different dates and timeout is on a business day and time part of timeout > 0700
then elapsed hours from 0700 on the date of timeout to min(timeout, 1700 on the date of timeout)
else 0 ;

hoursbetween = 10 * number of business days strictly between the dates of timein and timeout ;

result = firstdayhours + hoursbetween + lastdayhours;

You're right Amber. This process did not allow for same day delivery. Try:

-SET &calendar1 = '2011/01/04 12:53:12.00000';
-SET &calendar2 = '2011/01/04 16:22:09.00000';
 
SET BUSDAYS=_MTWTF_
DEFINE FUNCTION DELIVERY(startDate/HYYMDs,endDate/HYYMDs)
wdays/I6 = DATEDIF(HDATE(startDate, 'YYMD'),HDATE(endDate, 'YYMD'), 'BD');
adays/I6 = HDIFF(endDate,startDate, 'DAY', 'D6.0');
endMins/I8 = HDIFF(endDate,HSETPT(HSETPT(HSETPT(endDate,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',7,8,'HYYMDI'), 'MINUTE', 'D8.0');
startMins/I8 = HDIFF(HSETPT(HSETPT(HSETPT(startDate,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',17,8,'HYYMDI'),startDate,'MINUTE', 'D8.0');
day_hours/D8.1 = ((wdays-(adays-wdays)-1) *10);
hours/D8.1 = ((startMins+endMins)/60);
DELIVERY/D8.1 =IF day_hours LT 0 THEN hours-10 ELSE day_hours+hours;
END
 
DEFINE FILE FILE
startDate/HYYMDs = HINPUT(25, '&calendar1', 8,'HYYMDs');
endDate/HYYMDs = HINPUT(25, '&calendar2', 8,'HYYMDs');
time/D8.1 = IF endDate GT startDate THEN DELIVERY(startDate,endDate) ELSE 0;
END

I think we have it! Thanks for your help!!

DEFINE FUNCTION DELIVERY(TIMEIN/HYYMDS,TIMEOUT2/HYYMDS)
WDAYS/I6 = DATEDIF(HDATE(TIMEIN,'YYMD'),HDATE(TIMEOUT2, 'YYMD'), 'BD');
ADAYS/I6 = HDIFF(TIMEOUT2,TIMEIN,'DAY', 'D6.0');
ENDMINS/I8 = HDIFF(TIMEOUT2,HSETPT(HSETPT(HSETPT(TIMEOUT2,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',7,8,'HYYMDI'),'MINUTE','D8.0');
ENDTEST/D8 = IF ENDMINS LT 0 THEN 0 ELSE ENDMINS;
STARTMINS/I8 = HDIFF(HSETPT(HSETPT(HSETPT(TIMEIN,'SECOND',0,8,'HYYMDI'),'MINUTE',0,8,'HYYMDI'),'HOUR',17,8,'HYYMDI'),TIMEIN,'MINUTE', 'D8.0');
STARTTEST/I8 = IF STARTMINS LT 0 THEN 0 ELSE STARTMINS;
DELIVERY/D8.1 =IF ADAYS NE WDAYS THEN ((WDAYS-(ADAYS-WDAYS)+1)*10)+((STARTTEST+ENDTEST)/60) ELSE ((WDAYS-(ADAYS-WDAYS)-1)*10)+((STARTTEST+ENDTEST)/60);
END
DEFINE FILE ALLDIVS
TIME/D8.1 = DELIVERY(TIMEIN,TIMEOUT2);
END

Here's a slightly different approach. Note the absense of IF-THEN-ELSE constructs.

DEFINE FUNCTION HrsElaps(Btime/HYYMDS, Etime/HYYMDS)
-* get date of Begin and End date-time input values
  BDATE/YYMD=HDATE(Btime,'YYMD');
  EDATE/YYMD=HDATE(Etime,'YYMD');
-* get time, in sec.
  BTIME/D12=HTIME(8,Btime,'D12.2')/1000;
  ETIME/D12=HTIME(8,Etime,'D12.2')/1000;
-* day number + time as fraction of a day
  BTS/D20.4=BDATE + BTIME/(24*60*60);
  ETS/D20.4=EDATE + ETIME/(24*60*60);
-* end-points of workday, on beginning and ending dates
  B0700/D20.4=BDATE + 07/24;    B1700/D20.4=BDATE + 17/24;
  E0700/D20.4=EDATE + 07/24;    E1700/D20.4=EDATE + 17/24;
-* binary values (0 or 1):
-** Different dates?
  DIFF/I1S = BDATE LT EDATE;
-** Are begin and end dates work-days?
  BisBD/I1S= BDATE EQ DATEMOV(BDATE,'BD+');
  EisBD/I1S= EDATE EQ DATEMOV(EDATE,'BD+');
-* hours to count on beginning day, intermediated work-days, and ending day
-** count hours on Begin date only if it is a Work-day
  HOURS1/D12.4S=MAX(0, MIN(B1700,ETS)-MAX(BTS,B0700)) * (BisBD)*24;
-** to determine Work-days between the Begin and End dates, adjust begin and End dates to workday dates
  HOURS2/D12.4S=MAX(0, DATEDIF(DATEMOV(BDATE,'BD-'),DATEMOV(EDATE,'BD+'),'BD') -1)*10;
-** count hours on End date only if it is a Work-day and is different from the Begin date
  HOURS3/D12.4S=MAX(0, MIN(E1700,ETS)-E0700)          * (EisBD*DIFF)*24;
-*
  HrsElaps/D12.4S= HOURS1+HOURS2+HOURS3 ;
END

This works even if the assignment arrives or departs on a non-workday.