Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Max effective date with non future date

    Is it easy to convert the sql below to max effective date less than 7/1/2012?

    SELECT t.CHARTFIELD_VALUE, t.W_CF_ATTRIBUTE, t.W_CF_ATTRIB_VALUE, t.EFFDT, t.EFF_STATUS
    FROM (SELECT CHARTFIELD_VALUE, W_CF_ATTRIBUTE, MAX(EFFDT) AS latest FROM SYSADM_PS_X_S_M_CFATTRIB GROUP BY CHARTFIELD_VALUE, W_CF_ATTRIBUTE) AS m INNER JOIN SYSADM_PS_X_S_M_CFATTRIB AS t ON (m.CHARTFIELD_VALUE = t.CHARTFIELD_VALUE) AND (m.W_CF_ATTRIBUTE = t.W_CF_ATTRIBUTE) AND (m.latest = t.EFFDT)
    WHERE (((t.W_CF_ATTRIBUTE)="PROG" Or (t.W_CF_ATTRIBUTE)="ACT"))
    ORDER BY t.CHARTFIELD_VALUE;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably
    exclude all rows whose date is beyond the limit by adding another element to the where clause. eg:-
    Code:
    WHERE t.W_CF_ATTRIBUTE in ("PROG","ACT") and MyDateColumn <= #2012/01/07#
    that assumes you are using a 'sensible' date format as opposed to US mm/dd/yy
    the hash symbol (#) denotes the start and end of a date literal
    if the ISO date format of yyyy/mm/dd doesnt' work then use the US format #mm/dd/yyyy#
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Thanks where would I insert into the above? I find it difficult to incorporate and not leave out the ones that have a max effect date >7/1/2012.

    I would like to get this one to work also with that parameter if possible: (Select Max(EFFDT) From [SYSADM_PS_X_S_M_CFATTRIB] M WHERE M.CHARTFIELD_VALUE = [SYSADM_PS_X_S_M_CFATTRIB]![CHARTFIELD_VALUE] )

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't tested this in Jet, but PeopleSoft "effective date" logic is pretty standard across most platforms. I'd try using:
    Code:
    SELECT t.CHARTFIELD_VALUE, t.W_CF_ATTRIBUTE
    ,  t.W_CF_ATTRIB_VALUE, t.EFFDT, t.EFF_STATUS
       FROM SYSADM_PS_X_S_M_CFATTRIB AS t
       WHERE ((t.W_CF_ATTRIBUTE="PROG") OR (t.W_CF_ATTRIBUTE)="ACT"))
          AND t.EFFDT = (SELECT Max(m.EFFDT)
             FROM SYSADM_PS_X_S_M_CFATTRIB AS m
             WHERE  m.CHARTFIELD_VALUE = t.CHARTFIELD_VALUE
                AND m.W_CF_ATTRIBUTE = t.W_CF_ATTRIBUTE
                AND m.EFFDT <= #7/1/2012#)
       ORDER BY t.CHARTFIELD_VALUE;
    Especially if you're going against the OLTP PeopleSoft store (the "production database" to most folks), the performance for this query ought to be a whole bunch better too!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •