Re: Future Effective Dated rows

["Beller, Jay" <JBeller@LBISOFTWARE.COM>]

Unless I'm missing something Clint, the select, as you've coded it below
will return the Maximum EFFDT regardless of whether or not it's
future-dated. (H.EFFDT either less than or greater than or equal to today
does not exclude any values)

      (SELECT MAX(H.EFFDT)
       FROM   PS_JOB H
       WHERE  H.EMPLID    = C.EMPLID
         AND  H.EMPL_RCD# = C.EMPL_RCD#
         AND  (H.EFFDT    <= $AsOfDate
         OR    H.EFFDT    >= $AsOfDate))

-----Original Message-----
From: Lu, Clint [mailto:clint.lu@CAREMARK.COM]
Sent: Wednesday, May 16, 2001 11:16 AM
To: SQR-USERS@list.iex.net
Subject: Re: Future Effective Dated rows


Tony,

Just make a simple based on your select statement as like this. It will work
for you. I use this all the time in my programs.



begin-SELECT
A.EMPLID
B.EMPL_RCD#
C.EFFDT
etc...
FROM  PS_PERSONAL_DATA A,
      PS_EMPLOYMENT    B,
      PS_JOB           C,
      PS_DISABILITY     PSD,
      PS_PERS_NID NID
WHERE A.PER_STATUS = 'E'
  AND PSD.EMPLID=A.EMPLID
  AND NID.EMPLID = A.EMPLID
  AND B.EMPLID     = A.EMPLID
  AND C.EMPLID     = B.EMPLID
  AND C.EMPL_RCD#  = B.EMPL_RCD#
  AND C.EFFDT =
      (SELECT MAX(H.EFFDT)
       FROM   PS_JOB H
       WHERE  H.EMPLID    = C.EMPLID
         AND  H.EMPL_RCD# = C.EMPL_RCD#
         AND  (H.EFFDT    <= $AsOfDate
         OR    H.EFFDT    >= $AsOfDate))
  AND C.EFFSEQ =
      (SELECT MAX(I.EFFSEQ)
       FROM   PS_JOB I
       WHERE  I.EMPLID    = C.EMPLID
         AND  I.EMPL_RCD# = C.EMPL_RCD#
         AND  I.EFFDT     = C.EFFDT)



Clint Lu
Sr. Programmer/Analyst
Caremark Rx, Inc.
Phone: (847) 559-4717
Fax    : (847) 559-4840


-----Original Message-----
From: Dorman, Tony [mailto:Tony.Dorman@BELLSOUTH.COM]
Sent: Wednesday, May 16, 2001 9:54 AM
To: SQR-USERS@list.iex.net
Subject: Future Effective Dated rows


Hello,

I am seeking an answer to what I hope will be a simple question.  I am
attempting to capture maximum effective dated records that are either less
than the system date, or, if there are none, the MINIMUM effective dated row
that is future effective dated.  I've included the code that I thought would
do the trick, but the runtime is atrocious (my changes are enlarged, and in
bold).  What would be the most efficient way to code this?  We are on tools
7.55, using an Oracle database.   Any help would be appreciated.  Thanks.

begin-SELECT
A.EMPLID
B.EMPL_RCD#
C.EFFDT
etc...
FROM  PS_PERSONAL_DATA A,
      PS_EMPLOYMENT    B,
      PS_JOB           C,
      PS_DISABILITY     PSD,
      PS_PERS_NID NID
WHERE A.PER_STATUS = 'E'
  AND PSD.EMPLID=A.EMPLID
  AND NID.EMPLID = A.EMPLID
  AND B.EMPLID     = A.EMPLID
  AND C.EMPLID     = B.EMPLID
  AND C.EMPL_RCD#  = B.EMPL_RCD#
  AND C.EFFDT =
      ((SELECT MAX(H.EFFDT)
       FROM   PS_JOB H
       WHERE  H.EMPLID    = C.EMPLID
         AND  H.EMPL_RCD# = C.EMPL_RCD#
         AND  H.EFFDT    <= $AsOfDate)
         OR
       SELECT MIN(H.EFFDT)
       FROM PS_JOB G
         WHERE  G.EMPLID    = C.EMPLID
         AND  G.EMPL_RCD# = C.EMPL_RCD#
         AND  G.EFFDT   >=$AsOfDate))
  AND C.EFFSEQ =
      (SELECT MAX(I.EFFSEQ)
       FROM   PS_JOB I
       WHERE  I.EMPLID    = C.EMPLID
         AND  I.EMPL_RCD# = C.EMPL_RCD#
         AND  I.EFFDT     = C.EFFDT)