I have the below DB2 SQL that I am trying to retrun all 200,000 employees including those that have a History record pay changes:
A.HM_DIST_CO AS "Dist Company"
,VARCHAR(A.HM_ACCT_UNIT) AS "Dist Acct Unit"
,VARCHAR(A.HM_ACCOUNT) AS "Dist Account"
,A.EMP_STATUS AS "Status"
,A.TERM_DATE AS "Term Date"
,Z.COMPANY AS "HRH Company"
,VARCHAR(Z.EMPLOYEE) AS "HRH Employee"
,Z.FLD_NBR AS "HRH Fld Nbr"
,Z.N_VALUE AS "HRH N Value"
,Z.DATE_STAMP AS "HRH Date Stamp"
,Z.ACT_OBJ_ID AS "HRH Act Obj ID"
,C.HM_PHONE_NBR AS "Phone"
,C.SEX AS "Gender"
,C.BIRTHDATE AS "Birth Date"
JOIN DBHRPEM C
ON A.COMPANY = C.COMPANY
AND A.EMPLOYEE = C.EMPLOYEE
JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE
WHERE Z.FLD_NBR =730
AND Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y
WHERE Y.EMPLOYEE = Z.EMPLOYEE)
ORDER BY A.EMPLOYEE
The issue with the above SQL is that with the Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y WHERE Y.EMPLOYEE = Z.EMPLOYEE) statement only 7,100 records are returned.
How do I structure the DB2 SQL retruning records of those with pay changes and those that don't have pay reocrd changes to include all 200,000 employees?