i am doing a BY date of the data.
I am looking for data for 100 days from the START_DATE.
But there is no data for some of the days.
I want to know how to display data for 100 days ,if no records then display as NULL
Please reply immediately

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

Have you search the forum for missing row techniques?

One way is to use a Dialogue Manager REPEAT loop to generate a file with all of the dates that you need into a temp file then join this file (left-outer) to the data file.

Adithya,

Here is another technique that does essentially what Ginny suggests, but does not involve the use of Dialogue Manager to produce the list of dates. I use the CAR file in my example, but you must use a file that contains at least 100 rows (the CAR file does not) in order to generate a list of 100 consecutive dates (perhaps you can use the same table that contains your data). You may need to change the date formats I use here to correspond to the date format in your table.

-SET &START_DATE = '01/01/2009';
-*
DEFINE FILE CAR
START_DATE/MDYY WITH BODYTYPE = '&START_DATE';
TEMP_DATE/MDYY = IF START_DATE GT (LAST TEMP_DATE + 1) THEN START_DATE ELSE (LAST TEMP_DATE + 1);
END
-*
-* This will produce a hold file containing a list of 100 consecutive dates
-* (less than 100 in this case because the CAR file does not contain 100 records).
TABLE FILE CAR
PRINT TEMP_DATE NOPRINT
BY TEMP_DATE
WHERE RECORDLIMIT EQ 100
ON TABLE HOLD AS HOLDDATE
END
-*
-* Extract the data you need for the report.
TABLE FILE <your tablename>
PRINT/SUM <column names>
BY DATE
WHERE DATE GE '&START_DATE';
ON TABLE HOLD AS HOLDDATA
END
-*
-* Join your data output file to the hold file containing the list of 100 dates.
JOIN CLEAR *
JOIN TEMP_DATE IN HOLDDATE TO DATE IN HOLDDATA AS J1
-*
TABLE FILE HOLDDATE
PRINT <column names>
BY TEMP_DATE
ON TABLE SET ALL ON
END

You could also use the FOR statement or ROWS.
But this requires some data to exist.

-SET &STRTDATE = '800601' ;
TABLE FILE EMPLOYEE
PRINT CURR_SAL
FOR HIRE_DATE
-SET &Over = ' ' ;
-REPEAT DATELOOP FOR &CNTR FROM 0 TO 99 ;
-SET &NEWDATE = AYMD(&STRTDATE,&CNTR,'I6');
 &Over &NEWDATE
-SET &Over = 'OVER' ;
-DATELOOP
ON TABLE HOLD AS TMP_DATA
END

DEFINE FILE TMP_DATA
 HIRE_DATE/I6YMD = EDIT(E01);
END

TABLE FILE TMP_DATA
PRINT CURR_SAL
BY HIRE_DATE
END