×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

Peoplesoft: Query Manager - Max(EFFSEQ);

Peoplesoft: Query Manager - Max(EFFSEQ);

Peoplesoft: Query Manager - Max(EFFSEQ);

(OP)
How do I select the Last EFFSEQ while the EFFDATE falls within a date range?

RE: Peoplesoft: Query Manager - Max(EFFSEQ);

Hi Kkecia,

I assume that your question means you after the max date and sequence for a date within a date range.

If you use the default effdt/effseq sub queries, then the query will not work for you as that only allows for a single date for the max. You need to create your own sub queries in this case -
Create your first sub query looking a the date - and with your max date compare put in date range, for the second sub query you need the max sequence for the date you found in the first sub query.

So the basic code using PS_JOB will look like (simplified and on DB2 so not sure how your date formatting will look

SELECT A.EMPLID
, A.EMPL_RCD
, A.EFFDT
, A.EFFSEQ
FROM PS_JOB A
WHERE A.EFFDT = (SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.EFFDT BETWEEN '2013-01-01' AND '2013-06-30')
AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A.EMPLID = A2.EMPLID
AND A.EMPL_RCD = A2.EMPL_RCD
AND A.EFFDT = A2.EFFDT)

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