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 firstname.lastname@example.org for further assistance. Reference the community FAQ to learn more about the community.
First create a table with 0 to 23 where 1 occurs one time and 24 occurs 24 times. Then calculate with DTDIFF how many hours you have. Join this value to this hours table. In your case you'll get 5 records. Now you can calculate with starttime + the key in the hours table and endtime how many minutes you have in a hour segment.
Using a calendar table as a data-source for things like these is convenient. That's fairly easy to do in any RDBMS, but you could also create a FOCUS file or an Excel even. The same could be said for an hours table, which would allow you to add descriptions of each hour in your local preference (such as '1pm' instead of '13').
I came up with this bit, where the range spans several days:
TABLE FILE systable
COMPUTE STARTTIME/HYYMDSA = '2020/09/23 8:18:22PM';
COMPUTE ENDTIME/HYYMDSA = '2020/09/25 3:18:22AM';
COMPUTE STARTHOUR/I2 = DTPART(STARTTIME, HOUR);
COMPUTE STARTMINS/I2 = DTPART(STARTTIME, MINUTE);
COMPUTE STARTDATE/YYMD = HDATE(HMIDNT(STARTTIME, 8, 'HYYMD'), STARTDATE);
COMPUTE ENDHOUR/I2 = DTPART(ENDTIME, HOUR);
COMPUTE ENDMINS/I2 = DTPART(ENDTIME, MINUTE);
COMPUTE ENDDATE/YYMD = HDATE(HMIDNT(ENDTIME, 8, 'HYYMD'), ENDDATE);
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD AS yourdata
-* Create 24 hours
TABLE FILE systable
COMPUTE Hour24/I2 = Hour24 + 1;
WHERE TOTAL Hour24 LE 24;
ON TABLE HOLD AS hours
-* Create 24h/date (Carthesian product)
FILE calendar AT DATE TAG b TO ALL
FILE hours AT Hour24 TAG h AS J0
FILE calendar AT DATE TO UNIQUE
FILE yourdata AT NAME TAG y AS J1
TABLE FILE calendar
COMPUTE Minutes/I2 = IF b.DATE EQ y.STARTDATE AND h.Hour24 EQ y.STARTHOUR THEN y.STARTMINS
ELSE IF b.DATE EQ y.ENDDATE AND h.Hour24 EQ y.ENDHOUR THEN 60 - y.ENDMINS
ELSE IF y.STARTDATE EQ y.ENDDATE AND h.Hour24 FROM y.STARTHOUR TO y.ENDHOUR THEN 60
ELSE IF b.DATE GT y.STARTDATE AND b.DATE LT y.ENDDATE THEN 60
ELSE IF b.DATE EQ y.STARTDATE AND h.Hour24 GT y.STARTHOUR THEN 60
ELSE IF b.DATE EQ y.ENDDATE AND h.Hour24 LT y.ENDHOUR THEN 60
WHERE (b.DATE EQ y.STARTDATE AND h.Hour24 GE y.STARTHOUR -1) OR (b.DATE GT y.STARTDATE);
WHERE (b.DATE EQ y.ENDDATE AND h.Hour24 LE y.ENDHOUR +1) OR (b.DATE LT y.ENDDATE);
As an alternative to a calendar table, if you have start- and enddate ranges available as dialog manager variables, you could generate a date ranges table similar to how dbeagan created the hours table by using DATEADD, HADD or DTADD.This message has been edited. Last edited by: Wep5622,
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
Thanks for all of your responses. Hi Dave! Thanks a bunch, your method worked great. Didn't need to try the other suggestions because I'm all set. But good to know there are a number of ways to do this. Thanks everyone!