I have two files. FileA contains EMP_ID, BEGIN_DATE, END_DATE, and PAY_RATE. FileB contains EMP_ID, SICK_DATE, SICK_HOURS. The EMP_ID in both files are the same. The BEGIN_DATE and END_DATE represent personnel actions such as promotions, demotions, etc. The SICK_DATE field is the days the employee used sick leave. I need to determine what an employee PAY_RATE was on a specific date.

FileA
EMP_ID BEGIN_DATE END_DATE PAY_RATE
12476 20070104 20080202 34,987
12476 20060805 20070103 33,857
12476 20060623 20060804 33,857
12476 20050618 20060622 31,675

FileB
EMP_ID SICK_DATE SICK_HOURS
12476 20050620 7
12476 20050621 7
12476 20060721 7


What code can I use that will look at both of these files and determine on 20050620 and 20050621 the employee PAY_RATE was 31,675 and on 20060721 the PAY_RATE was 33,857. The SICK_DATE falls between the BEGIN_DATE AND END_DATE, but I need to see this all on one report.

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

This should do it.

TABLE FILE FILEA
PRINT PAY_RATE
BY EMP_ID
BY BEGIN_DATE
BY END_DATE
ON TABLE HOLD AS PAYHOLD FORMAT FOCUS INDEX EMP_ID
END
JOIN EMP_ID IN FILEB TO ALL EMP_ID IN PAYHOLD AS J1
TABLE FILE FILEB
PRINT *
WHERE SICK_DATE GE BEGIN_DATE AND SICK_DATE LE END_DATE
END

I was hoping to see the actual dates between the begin_date and end_date. So the report would produce the following results:
12476 20070104 34,987
12476 20070105 34,987
12476 20070106 34,987
etc.
12476 20080202 34,987

Next would be 20060805 to 20070103.
12476 20060805 33,857
12476 20060806 33,857
12476 20060807 33,857
etc.
12476 20070103 33,857

I have a time and attendance file by date that I want to join the information with. My join statement would be EMP_ID | DATE.

quote:

Originally posted by bsmith:
I was hoping to see the actual dates between the begin_date and end_date. So the report would produce the following results:
12476 20070104 34,987
12476 20070105 34,987
12476 20070106 34,987
etc.
12476 20080202 34,987

Next would be 20060805 to 20070103.
12476 20060805 33,857
12476 20060806 33,857
12476 20060807 33,857
etc.
12476 20070103 33,857

I have a time and attendance file by date that I want to join the information with. My join statement would be EMP_ID | DATE.

Assuming your attendance file has record for each date/day you want in your final result,
You can take GinnyJakes'S solution one step forward to accomplish that.

Generate Hold file as mentioned in previous post

"TABLE FILE FILEA
PRINT PAY_RATE
BY EMP_ID
BY BEGIN_DATE
BY END_DATE
ON TABLE HOLD AS PAYHOLD FORMAT FOCUS INDEX EMP_ID
END
JOIN EMP_ID IN FILEB TO ALL EMP_ID IN PAYHOLD AS J1
TABLE FILE FILEB
PRINT *
WHERE SICK_DATE GE BEGIN_DATE AND SICK_DATE LE END_DATE
ON TABLE HOLD FORMAT FOCUS INDEX EMP_ID
END"

JOIN CLEAR *
JOIN EMP_ID IN ATTENDANCEFILE TO ALL EMP_ID IN HOLD AS J2

TABLE FILE ATTENDANCEFILE
PRINT *
WHERE ATTENDANCE_DATE GE BEGIN_DATE AND ATTENDANCE_DATE LE END_DATE
END.

THIS IS ONE WAY TO INCREASE ROWS TO SHOW EACH DAY IN YOUR ATTENDANCE FILE ALONG WITH PAYRATE, SICK DATE ETC.

B_Smith,

Please update your signature.

Your first post says that you only want to see the PAY_RATE for each SICK_DATE. Ginny gave you an example.

Your second post says that you want to create a file with all days for each EMP_ID. This is something the the McGuyver technique does best. I suggest you search the forum for that. If you don't find/understand, come back here and we can give you an example.