I want to be able to produce a report from a data source that includes a date/time stamp, and calculate metrics by time slices. A time slice could be an hour range (7:00 AM - 8:00 AM) or even half-hour or quarter-hour.

My report needs to display all possible time slices for a particular day, even if there is no data in the data sources that fits.

This is an example of what I mean:

TIME SALES
12:00 AM $350
1:00 AM $225
2:00 AM $0 (no records in source)
3:00 AM $0 (no records in source)
4:00 AM $0 (no records in source)
5:00 AM $33
6:00 AM $150

and so on.

I am able to do this, and I am include sample code below to show how, but I am wondering if there is another way to handle it. The key issue I have is that I need to make sure all time slices show even if the data does not exist.

Here is my sample code. The process uses the McGyver technique to create a file structure for each time slice, then joining the result of that to the file that contains the data to produce the final report.

-* File query_audit_timestamp.fex
-SET &ECHO = 'ALL';

SQL
SELECT 1                AS NUM_JOBS,
       AUDIT_END_TMSTMP AS AUD_TIME_STAMP
  FROM ODSADM.ODS_UTIL_ETL_audit
 WHERE AUDIT_END_TMSTMP >= TO_DATE('03/04/2013','MM/DD/YYYY')
   AND AUDIT_END_TMSTMP < TO_DATE('03/05/2013','MM/DD/YYYY')
 ORDER BY AUDIT_END_TMSTMP
;
TABLE
ON TABLE HOLD
END
-RUN

JOIN BLANK WITH NUM_JOBS IN HOLD TO BLANK IN FSEQ
DEFINE FILE HOLD
BLANK/A1 WITH NUM_JOBS = ' ';

RPT_TIME_HOUR/A8 = DECODE COUNTER ( 1 '12:00 AM'
                                    2 '01:00 AM'
                                    3 '02:00 AM'
                                    4 '03:00 AM'
                                    5 '04:00 AM'
                                    6 '05:00 AM'
                                    7 '06:00 AM'
                                    8 '07:00 AM'
                                    9 '08:00 AM'
                                   10 '09:00 AM'
                                   11 '10:00 AM'
                                   12 '11:00 AM'
                                   13 '12:00 PM'
                                   14 '01:00 PM'
                                   15 '02:00 PM'
                                   16 '03:00 PM'
                                   17 '04:00 PM'
                                   18 '05:00 PM'
                                   19 '06:00 PM'
                                   20 '07:00 PM'
                                   21 '08:00 PM'
                                   22 '09:00 PM'
                                   23 '10:00 PM'
                                   24 '11:00 PM'
                                 ELSE '');

END
TABLE FILE HOLD
SUM
   RPT_TIME_HOUR
BY COUNTER 
WHERE COUNTER LE 24
ON TABLE HOLD AS TIMEFILE FORMAT XFOCUS
END
-RUN

DEFINE FILE HOLD
RPT_DATE/A20          = HCNVRT(AUD_TIME_STAMP, '(HYYMDS)', 20, 'A20');
RPT_HOUR/A2           = EDIT(RPT_DATE,'$$$$$$$$$$$99');
RPT_HOUR_INT/I2       = EDIT(RPT_HOUR);
RPT_AMPM/A2           = IF RPT_HOUR LT '12' THEN 'AM' ELSE 'PM';
RPT_HOUR_ADJ/I2       = IF RPT_AMPM EQ 'PM' THEN RPT_HOUR_INT - 12 ELSE RPT_HOUR_INT;
RPT_HOUR_ADJ2/A2      = EDIT(RPT_HOUR_ADJ);
RPT_TIME_HOUR/A8      = IF RPT_HOUR EQ '00' AND RPT_AMPM EQ 'AM' THEN '12:00 AM' ELSE 
                        IF RPT_HOUR EQ '12' AND RPT_AMPM EQ 'PM' THEN '12:00 PM' ELSE RPT_HOUR_ADJ2 | ':' | '00' | ' ' | RPT_AMPM;
RPT_TIME_HALF_HOUR/A8 = IF RPT_HOUR EQ '00'                      THEN '12:00 AM' ELSE
                        IF RPT_MINS GE '00' AND RPT_MINS LT '30' THEN RPT_HOUR_ADJ2 | ':' | '00' | ' ' | RPT_AMPM ELSE RPT_HOUR_ADJ2 | ':' | '30' | ' ' | RPT_AMPM;
RPT_TIME_QTR_HOUR/A8  = IF RPT_HOUR EQ '00'                      THEN '12:00 AM' ELSE
                        IF RPT_MINS GE '00' AND RPT_MINS LT '15' THEN RPT_HOUR_ADJ2 | ':' | '00' | ' ' | RPT_AMPM ELSE
                        IF RPT_MINS GE '15' AND RPT_MINS LT '30' THEN RPT_HOUR_ADJ2 | ':' | '15' | ' ' | RPT_AMPM ELSE
                        IF RPT_MINS GE '30' AND RPT_MINS LT '45' THEN RPT_HOUR_ADJ2 | ':' | '30' | ' ' | RPT_AMPM ELSE RPT_HOUR_ADJ2 | ':' | '45' | ' ' | RPT_AMPM;
END
TABLE FILE HOLD
SUM
   NUM_JOBS
BY RPT_HOUR NOPRINT
BY RPT_TIME_HOUR
ON TABLE HOLD AS JOBSFILE FORMAT XFOCUS INDEX RPT_TIME_HOUR
END
-RUN

JOIN RPT_TIME_HOUR IN TIMEFILE TO RPT_TIME_HOUR IN JOBSFILE
TABLE FILE TIMEFILE
SUM
   NUM_JOBS
BY COUNTER NOPRINT
BY RPT_TIME_HOUR
-*BY RPT_TIME_HALF_HOUR
-*BY RPT_TIME_QTR_HOUR
END

Any ideas out there?

This message has been edited. Last edited by: JRLewis,

Here is a suggestion that uses Date Time functions and the ROWS commsnd.

DEFINE FILE CAR
 AHour/A3 = EDIT(SEATS) ;
 Mins/I2 WITH MODEL  = IF LAST Mins GT 53 THEN  LAST Mins - 50 ELSE LAST Mins + 7 ;
 AMins/A2 = EDIT(Mins) ;
 Timestamp/HYYMDS = HINPUT(14,'&YYMD' | EDIT(AHour,'$99') | AMins | '00',8,Timestamp) ;
 Hour/D2 = HPART(Timestamp, 'HOUR', Hour) ;
END

TABLE FILE CAR
 PRINT 
 Timestamp
 MODEL
 BY Hour
    ROWS  0 OVER  1 OVER  2 OVER  3 OVER  4 OVER  5 OVER  6 OVER  7 OVER  8 OVER  9 OVER 10 OVER 11
    OVER 12 OVER 13 OVER 14 OVER 15 OVER 16 OVER 17 OVER 18 OVER 19 OVER 20 OVER 21 OVER 22 OVER 23
 ON TABLE HOLD AS HLD_TIME
END
-RUN
DEFINE FILE HLD_TIME
 GenHour/I2 = EDIT(E01) ;
 Hour/HHA = HINPUT(14,'00000000' | EDIT(GenHour) | '0000',8,Hour);
END

TABLE FILE HLD_TIME
 PRINT
 MODEL
 BY Hour
END

Waz,

Looks like a good option - haven't run across the ROWS command before.

Thanks!

Waz,

Do you know of any limitations on the number of ROWS?

Not only do I need to report on an hourly basis, but I will also need to report in half-hour and quarter-hour increments for a particular day.

I don't believe so.

I have done a couple of hundred items my self.

You could use Dialog Manager to create this nad have it parameterised to handle Hour/Half or Quarter.

You will have to do more work on the way it works, as I have taken the easy way with hours being easy to identify.

The ROWS command is a good option for what I am doing, and I plan on using it. Thanks for the tip!