Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Time Duration Intervals
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Time Duration Intervals
 Login/Join
 
Member
posted
I have a list of employees with start time and end time in date time format (HYYMDSA).

Start time
2020/09/23 3:18:22PM

End time
2020/09/23 8:18:22PM

I need to split this out into 60 minute intervals by hour.

3pm hour - 42 mins
4pm hour - 60 mins
5pm hour - 60 mins
etc.

Is there a way to do this without writing a conditional statement for each time slot?

This message has been edited. Last edited by: FP Mod Chuck,


8202
 
Posts: 5 | Registered: June 05, 2018Reply With QuoteReport This Post
Guru
posted Hide Post
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.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
This could work:
 TABLE FILE systable
 PRINT NAME
 COMPUTE STARTTIME/HYYMDSA = '2020/09/23 3:18:22PM';
 COMPUTE ENDTIME/HYYMDSA   = '2020/09/23 8:18:22PM';
 WHERE READLIMIT IS 1
 ON TABLE HOLD AS yourdata
 END

 TABLE FILE systable
   PRINT NAME 
 COMPUTE Hour24/I2 = Hour24 + 1;
 WHERE TOTAL Hour24 LE 24;
 ON TABLE HOLD AS hours
 END

 JOIN FILE yourdata AT NAME TO ALL FILE hours AT Hour24 AS J1
 END

 TABLE FILE yourdata
   PRINT STARTTIME
         ENDTIME
 COMPUTE DateMidnight/HYYMDSA = HDATE(STARTTIME, 'YYMD');
 COMPUTE DateHour/HYYMDSA     = DTADD(DateMidnight, HOUR, Hour24 - 1);
 COMPUTE ClockHour/I2         = MOD(Hour24 + 10, 12) + 1;
 COMPUTE AMPM/A2              = IF Hour24 LE 12 THEN 'am' ELSE 'pm';
 COMPUTE MinutesFromStart/I2  = DTDIFF(DateHour, STARTTIME, MINUTE) + 60;
 COMPUTE MinutesToEnd/I2      = DTDIFF(ENDTIME, DateHour, MINUTE);
 COMPUTE MinutesFrom60/I2     = IF MinutesFromStart LT 0 THEN 0 ELSE IF MinutesFromStart GT 60 THEN 60 ELSE MinutesFromStart;
 COMPUTE Minutesto60/I2       = IF MinutesToEnd     LT 0 THEN 0 ELSE IF MinutesToEnd     GT 60 THEN 60 ELSE MinutesToEnd;
 COMPUTE Minutes/I2           = MIN(MinutesFrom60, Minutesto60);
 COMPUTE IntervalMinutes/A29  = FPRINT(ClockHour, 'I2', 'A9') || AMPM | ' hour - ' | FPRINT(Minutes, 'I2', 'A2') | ' mins';
 WHERE TOTAL Minutes GT 0;
 END 


WebFOCUS 8.2.06
 
Posts: 204 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
PRINT NAME
	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
END

-* Create 24 hours
TABLE FILE systable
PRINT
	NAME 
	COMPUTE Hour24/I2 = Hour24 + 1;

WHERE TOTAL Hour24 LE 24;
ON TABLE HOLD AS hours
END

-* Create 24h/date (Carthesian product)
JOIN
	FILE calendar AT DATE TAG b TO ALL
	FILE hours AT Hour24 TAG h AS J0
END
JOIN
	FILE calendar AT DATE TO UNIQUE
	FILE yourdata AT NAME TAG y AS J1
END
TABLE FILE calendar
PRINT
	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
		ELSE 0;
BY b.DATE
BY h.Hour24

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);
END


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 :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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!


8202
 
Posts: 5 | Registered: June 05, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Time Duration Intervals

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.