

Go  New  Search  Notify  Tools  Reply 
Member 
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  

Guru 
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.  

Platinum Member 
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  

Virtuoso 
Using a calendar table as a datasource 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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Member 
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  

Powered by Social Strata 
Please Wait. Your request is being processed... 