I have used the WebFocus EMPDATA file to give an example of what I am trying to do: I need to create a report that summarizes the number of employees hired on each date. However, for dates that there were no employees hired, I need the date to still show up on my report with a total of "0" in the count field. As it is now, it only shows dates that have any data in the data source causing tons of missing dates.

I saw the examples using the car file, but it is using text and not dates and I can't seem to get the techniques used there to make it work with dates. Has anyone ever done this before?

-* File empdata.fex
TABLE FILE EMPDATA
SUM
     CNT.EMPDATA.EMPDATA.PIN AS 'COUNT,EMPDATA.EMPDATA.PIN'
BY  LOWEST EMPDATA.EMPDATA.HIREDATE
WHERE EMPDATA.EMPDATA.HIREDATE GE '89/01/01' AND EMPDATA.EMPDATA.HIREDATE LE '89/05/01';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
  

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

I am not sure what example you found but if the example uses alphanumeric could try changing the dates into alphanumeric using the A8YYMD format or the function HCNVRT.

I found an example on here to make a hold file of the entire list of dates between two dates, but once I have the hold file, I can't figure out how to join it to my data. It makes my server crash when I try to join the date to anything. How do I use my hold file to create the list of "By" values, or am I thinking all wrong about this?

-RUN
APP FILEDEF DATEMAS DISK datedata.mas
-RUN
-WRITE DATEMAS FILENAME=DATEDATA,SUFFIX=FIX
-WRITE DATEMAS SEGNAME=DATEDATA,SEGTYPE=S0
-WRITE DATEMAS FIELDNAME=DATEVAL ,ALIAS=DATEVAL ,FORMAT=YYMD, ACTUAL=A8,$
APP FI DATEDATA DISK datedata.ftm
-RUN
-REPEAT ENDREPEAT1 FOR &I FROM 0 TO &DIFF1
-SET &DATEVAL = AYMD(&SDATEX, &I, 'I8');
-WRITE DATEDATA &DATEVAL
-IF &DATEVAL EQ &EDATEX GOTO QUITREPEAT1 ;
-ENDREPEAT1
-QUITREPEAT1
-RUN

TABLE FILE DATEDATA
PRINT
DATEVAL
ON TABLE HOLD AS ALLDATES
END
  

Here is a method that uses the FOR statement to populate the missing data.

-SET &STRTDATE = '890101' ;
-SET &FNSHDATE = '890501' ;

-SET &DAY_STRT = DAYMD(&STRTDATE,'I8') - 365 ;
-SET &NO_DAYS  = YMD(&STRTDATE,&FNSHDATE) ;
-SET &DAY_FNSH = &DAY_STRT + &NO_DAYS - 1 ;

-TYPE &DAY_STRT &NO_DAYS

DEFINE  FILE EMPDATA
 DATE_NO/I9 = HIREDATE
END

TABLE   FILE EMPDATA
 PRINT  
        PIN
        HIREDATE

-* BY    DATE_NO
 FOR    DATE_NO
-SET &Over = ' ' ;
-REPEAT FILLDATE FOR &CNTR FROM &DAY_STRT TO &DAY_FNSH ;
 &Over &CNTR
-SET &Over = 'OVER' ;
-FILLDATE

 WHERE  EMPDATA.EMPDATA.HIREDATE GE '&STRTDATE' AND EMPDATA.EMPDATA.HIREDATE LE '&FNSHDATE';

 ON     TABLE HOLD AS DATEPREP

END

DEFINE FILE DATEPREP
 DATE_CNTR/I9 = IF PIN GT ' ' THEN 1 ELSE 0 ;
 DATE_NO/I9 = EDIT(E01) ;
 BASEDATE/YYMD = &STRTDATE ;
 HIREDATE/YYMD = BASEDATE + DATE_NO ;
END

TABLE FILE DATEPREP
SUM
     DATE_CNTR AS 'COUNT,EMPDATA.EMPDATA.PIN'
BY  HIREDATE

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

I finally got it working with the help of this other posting after some tweaking.