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
September 24, 2020, 04:55 AM
Frans
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.
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
September 25, 2020, 05:07 AM
Wep5622
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 :
September 25, 2020, 10:58 AM
SBanks
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!