Effective Dates/Sequence/Status

Effective dates, sequences and statuses are perhaps one of the most confusing aspects of PeopleSoft. This article is intended to help clear things up, but like anything I write, I could be off the mark, so please feel free to discuss this article in the forums.

The typical pairing combinations you might see in records are:

  • Effective Date (EFFDT) and Effective Sequence (EFFSEQ)
  • Effective Date (EFFDT) and Effective Status (EFF_STATUS)

If you are using EFFDT by itself as a key you might want to think about whether it is feasible for data to be entered on the same date. That is, should you be using EFFSEQ or EFF_STATUS? If you're not sure, read on …

You might have noticed that EFFDT is in both combinations. This is literally just storing the effective date of a row. I like to think of this as the date on which the row was effective as in when it started to be of any consequence. In this sense an effective date can be in the future. E.g. this person's address is effective as of 1 January 2009. That is, until 1 January 2009, this address isn't of any consequence. As a side, if you get confused between effect and affect, check out this article.

Because effective date is always involved, the important distinction to make is between effective sequence and effective status. The way I like to think about this is:

  • Effective sequence tracks the sequence of changes in rows for transactional history
  • Effective status indicates which row is currently active and which rows were previously active (inactive) for configuration history

The general trend you'll find in PeopleSoft is effective sequence is used in transactional tables and effective status is used in configuration tables. However, as with many aspects of PeopleSoft this distinction can be a bit hazy! Effective status is also used in tables where you should technically only have one combination of key fields active at any time (e.g. a person's name or address).

Getting the current Row (EFFDT & EFFSEQ)

For this example, I'm using the campus solutions record PS_ACAD_PROG (student's academic program) which essentially stores the programs (think of these as degrees) that a student is currently studying.

The keys on PS_ACAD_PROG are:

  • EMPLID
  • ACAD_CAREER
  • STDNT_CAR_NBR
  • EFFDT
  • EFFSEQ

To get the current row you need to find the maximum effective dated row, and then the maximum effective sequence for that effective date.

So you'll need to write SQL that looks like this. Note the following:

  • The joins are on the keys excluding EFFDT and EFFSEQ
  • The effective date is less or equal to the current date (%CurrentDateIn)
  • The maximum sequence is determined for the maximum effective date returned
select PRG.*
from
    PS_ACAD_PROG PRG
where
    PRG.EFFDT = (
        select  max(PRG_ED.EFFDT)
        from    PS_ACAD_PROG PRG_ED
        where   PRG_ED.EMPLID = PRG.EMPLID
        and     PRG_ED.ACAD_CAREER = PRG.ACAD_CAREER
        and     PRG_ED.STDNT_CAR_NBR = PRG.STDNT_CAR_NBR
        and     PRG_ED.EFFDT <= %CurrentDateIn
    )
    and PRG.EFFSEQ = (
        select  max(PRG_ES.EFFSEQ)
        from    PS_ACAD_PROG PRG_ES
        where   PRG_ES.EMPLID = PRG.EMPLID
        and     PRG_ES.ACAD_CAREER = PRG.ACAD_CAREER
        and     PRG_ES.STDNT_CAR_NBR = PRG.STDNT_CAR_NBR
        and     PRG_ES.EFFDT = PRG.EFFDT
    )
;

Getting the current Row (EFFDT & EFF_STATUS)

For this example, I'm using the record, PS_ADDRESSES (person addresses) which stores addresses for a particular person. Note a person can have multiple address types (e.g. home address, mailing address, business address).

The keys on PS_ADDRESSES are:

  • EMPLID
  • ADDRESS_TYPE
  • EFFDT

Should EFF_STATUS be a key field here? No - here's why:

EMPLID ADDRESS_TYPE EFFDT EFF_STATUS
1234567 HOME 01/01/2008 A
1234567 HOME 01/01/2008 A Duplicate key
1234567 HOME 01/01/2008 I Duplicate key

Note A = Active, I = Inactive (translates)

The last two rows are both key violations - the same person, address type and effective date with active and inactive addresses. Leaving EFF_STATUS as a non-key field prevents this scenario. However, you'll find there are cases where EFF_STATUS has been used as a key field (perhaps even by people in your own organisation!):

select distinct RECNAME 
from PSRECFIELD where FIELDNAME = 'EFF_STATUS'
and bitand(USEEDIT, 1) > 0;

To get the current row, we need to clarify what we mean by current row:

  • Do we want the max effective dated row regardless of whether its active or inactive?
  • Do we want the max effective dated row that is active?
  • Do we want the max effective dated only if the max effective dated row is active?

Yes, points 2 and 3 are different. Here's some example data and an explanation:

EMPLID ADDRESS_TYPE EFFDT EFF_STATUS
1234567 HOME 01/01/2008 I
1234567 HOME 01/01/2007 A
1234567 HOME 01/01/2006 A

Based on this data the result are:

  • Max effective dated row regardless of whether it is active or inactive = HOME, 01/01/2008, I.
  • Max effective row that is active = HOME, 01/01/2007, A.
  • Max effective dated row if it is active = null, this doesn't exist.

Here's how you would write the SQL for each case. It's up to you to decide which one is most appropriate. Note that for point 3 (max effective dated row if it is active), you can have no data returned at all. This may cause subtle problems depending on what you are trying to do.

(1) Max effective dated row regardless of status

select ADR.*
from PS_ADDRESSES ADR
where EFFDT = (
        select  MAX(ADR_ED.EFFDT)
        from    PS_ADDRESSES ADR_ED
        where   ADR_ED.EMPLID = ADR.EMPLID
        and     ADR_ED.ADDRESS_TYPE = ADR.ADDRESS_TYPE
        and     ADR_ED.EFFDT <= %CurrentDateIn
);

(2) Max effective dated row that is active

select ADR.*
from PS_ADDRESSES ADR
where EFFDT = (
        select  MAX(ADR_ED.EFFDT)
        from    PS_ADDRESSES ADR_ED
        where   ADR_ED.EMPLID = ADR.EMPLID
        and     ADR_ED.ADDRESS_TYPE = ADR.ADDRESS_TYPE
        and     ADR_ED.EFFDT <= %CurrentDateIn
        and     ADR_ED.EFF_STATUS = 'A'
);

(3) Max effective dated row only if it is active

select ADR.*
from PS_ADDRESSES ADR
where EFFDT = (
        select  MAX(ADR_ED.EFFDT)
        from    PS_ADDRESSES ADR_ED
        where   ADR_ED.EMPLID = ADR.EMPLID
        and     ADR_ED.ADDRESS_TYPE = ADR.ADDRESS_TYPE
        and     ADR_ED.EFFDT <= %CurrentDateIn        
) and ADR.EFF_STATUS = 'A';

Spot the difference between (2) and (3)? The effective status is active check is in the subquery in (2) and outside the subquery in (3). This will give you different results - specifically (3) will exclude any cases where the maximum effective dated row is inactive while (2) will not. It will just bring back the row before the maximum effective dated inactive one, which is active.

So the trick here is to know whether or not you want data that is considered to be inactive because the maximum effective dated row is inactive. This is particularly important in configuration tables! The last thing you want is to work with configuration data that is considered inactive …

sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License