Re: Future Effective Dated rows

[Carlton Conley <cconley@CDG-INC.COM>]

might try a UNION getting the max effdt row  less that the AsOfDate, with
the second select getting the min(effdt) that are greater than the AsOfDate
minus the same select you started with so to subtract all those that have
future dated rows and a current row.

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of John Letourneau
Sent: Wednesday, May 16, 2001 8:34 AM
To: SQR-USERS@list.iex.net
Subject: Re: 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)