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.
New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.
What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.
During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.
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!