Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Time Duration Intervals
 Go New Search Notify Tools Reply
 [SOLVED] Time Duration Intervals
Member
 posted September 23, 2020 03:19 PM
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, 2018 IP
Guru
 posted September 24, 2020 04:55 AM 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, 2007 IP
Platinum Member
 posted September 24, 2020 03:14 PM 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, 2010 IP
Virtuoso
 posted September 25, 2020 05:07 AM 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, 2010 IP
Member
 posted September 25, 2020 10:58 AM 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, 2018 IP