×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Show Current Benefit Program and Previous Benefit Program

Show Current Benefit Program and Previous Benefit Program

Show Current Benefit Program and Previous Benefit Program

(OP)
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

RE: Show Current Benefit Program and Previous Benefit Program

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close