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,

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.

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 

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,

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!