I am trying to create a report that shows selected YTD accumulators for everyone. We have three paygroups, I am building my report using only one for simplicity and testing. The problem is that I have two employees in this paygroup who were active as of 12/31/10, but have since terminated. Their YTD information as of 12/31/10 is not showing in the report. I am using ReportSmith 3.1.

Here is the SQL:

SELECT
"PS_EMPLOYEES"."EMPLID", "PS_EMPLOYEES"."NAME", "PS_EMPLOYEES"."PAYGROUP", "PS_EMPLOYEES"."FILE_NBR", "PS_AL_YTD_ACCUM"."ACCUM_AMOUNT", "PS_AL_YTD_ACCUM"."ACCUM_NBR", "PS_AL_YTD_ACCUM"."AL_PROCESS_EFFDT"
FROM
"PS_EMPLOYEES", "PS_AL_YTD_ACCUM"
WHERE
(((("PS_EMPLOYEES"."EFFSEQ"= (
    SELECT MAX("INNERALIAS"."EFFSEQ")
    FROM "PS_EMPLOYEES" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_EMPLOYEES"."EMPLID"
    AND "INNERALIAS"."EFFDT" = "PS_EMPLOYEES"."EFFDT")
AND
"PS_EMPLOYEES"."EFFDT" = (
    SELECT MAX("INNERALIAS"."EFFDT")
    FROM "PS_EMPLOYEES" INNERALIAS
    WHERE "INNERALIAS"."EMPLID" = "PS_EMPLOYEES"."EMPLID"
    AND "INNERALIAS"."EFFDT" <=  TO_DATE('2010-12-31', 'YYYY-MM-DD')))) AND
("PS_EMPLOYEES"."PAYGROUP" = '8DG') AND
("PS_AL_YTD_ACCUM"."AL_PROCESS_EFFDT" = TO_DATE('2010-12-28', 'YYYY-MM-DD')) AND
("PS_AL_YTD_ACCUM"."ACCUM_NBR" IN( 'AD', 'CN', 'CX', 'C3', 'DK', 'DP', 'DU', 'D4', 'EC', 'EQ', 'EV', 'E5', 'E9', 'FD', 'FH', 'R2', 'R6', 'C7', 'AH')) AND
("PS_AL_YTD_ACCUM"."WEEK_NBR" = '52')))
AND
("PS_AL_YTD_ACCUM"."FILE_NBR" = "PS_EMPLOYEES"."FILE_NBR" )
GROUP BY
"PS_EMPLOYEES"."EMPLID", "PS_EMPLOYEES"."NAME", "PS_EMPLOYEES"."PAYGROUP", "PS_EMPLOYEES"."FILE_NBR", "PS_AL_YTD_ACCUM"."ACCUM_AMOUNT", "PS_AL_YTD_ACCUM"."ACCUM_NBR", "PS_AL_YTD_ACCUM"."AL_PROCESS_EFFDT"
ORDER BY
"PS_AL_YTD_ACCUM"."ACCUM_NBR"

I believe the problem lies in the effective date of the PS_EMPLOYEES table but I'm not sure how to indicate I need all employees and selected accumulators, whether they were active or not on 12/31. Thanks for any help!