Peoplesoft: Query Manager - Max(EFFSEQ);

[kkecia]

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

 

[notadba]

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)