Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Counting date/time M-F; 7:00 - 17:00 only

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Counting date/time M-F; 7:00 - 17:00 only
 Login/Join
 
Gold member
posted
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,


WebFOCUS 768
OS/400
HTML, also quite a few active reports and excel.
 
Posts: 59 | Registered: June 18, 2009Report This Post
Virtuoso
posted Hide Post
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);
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
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.


WebFOCUS 768
OS/400
HTML, also quite a few active reports and excel.
 
Posts: 59 | Registered: June 18, 2009Report This Post
Expert
posted Hide Post
You could use the HPART function in a DEFINE to extract the Hour from the date-time fields and then filter on this field...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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)
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
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);


WebFOCUS 768
OS/400
HTML, also quite a few active reports and excel.
 
Posts: 59 | Registered: June 18, 2009Report This Post
Virtuoso
posted Hide Post
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;
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
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


WebFOCUS 768
OS/400
HTML, also quite a few active reports and excel.
 
Posts: 59 | Registered: June 18, 2009Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Counting date/time M-F; 7:00 - 17:00 only

Copyright © 1996-2020 Information Builders