Show Current Benefit Program and Previous Benefit Program

[scotton]

Okay, below is the sql statement that I have in the selections portion of reportsmith. I am trying to display an employees current benefit program and their previous benefit program (if they have one). What's happening is I'm getting multiple rows for some people, not everyone. I know it's probably a simple fix but I've played around with the code and I haven't been able to make it work. HELP!

("PV_BEN_PROG_VW2"."EFFSEQ"= (
SELECT MAX("INNERALIAS"."EFFSEQ")
FROM "SYSADM"."PV_BEN_PROG_VW" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PV_BEN_PROG_VW2"."EMPLID"
AND "INNERALIAS"."EFFDT" < "PV_BEN_PROG_VW2"."EFFDT")
AND
"PV_BEN_PROG_VW2"."EFFDT" < (
SELECT MAX("INNERALIAS"."EFFDT")
FROM "SYSADM"."PV_BEN_PROG_VW" INNERALIAS
WHERE "INNERALIAS"."EMPLID" = "PV_BEN_PROG_VW2"."EMPLID"))

TIA,

Sarah

 

[CharlesCook]

This is a lot harder then you may think. I would use 2 copies of the PV_BEN_PROG_VW2 table. The first would return the current benefit program.

Then I would add another copy to return the previous benefit program. You will need to link the 2 tables and deal with them not having a previous benefit program. See thread131-1273130 for an idea of how to do this.

You will need to modify the effective dating of the second table to get the max -1 record see thread131-1191545 , thread131-1174051 , thread131-1162907 , thread131-901526 for some ideas.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining