Re: SQL Question

The following is what I would do using SQL Server. However, you would need to translate it into SQL*PLus for Oracle.

Select

    a.start_date,
    (

        select
            max(b.startdate)
        from
            encounters b
        where
            a.pat_id=b.patid and
            b.startdate<a.startdate

    ...
where

    ....

I know you can reproduce the from and where clause yourself. This method uses what's called a "correlated subquery", and I believe it's supported by Oracle.

"Gigi Lipori" <pflugg_at_bellsouth.net> wrote in message news:3d8b1c20$1_3_at_corp-news.newsgroups.com...
> I have query a, which works great with one exception. It outputs results
> b.What I'm trying to do is print the encounter of interest, and note the
> previous encounter next to it. I want to get a record that would show his
> 6/13 encounter with a blank for his previous encounter. I thought by doing
> an outer join to the Encounters alias I could accomplish that, but it
> doesn't work. Anyway, I think I'm being obtuse, but I can't get it. Any
> thoughts would be appreciated.
>
> QUERYA
>
> SELECT
> ENCOUNTERS.START_DATE,
> max(ENCOUNTERS2.START_DATE)
> FROM
> ENCOUNTERS,
> ENCOUNTERS ENCOUNTERS2,
> EXT_PAT_IDS,
> PATIENT_DEMO
> WHERE
> ( PATIENT_DEMO.PAT_ID=EXT_PAT_IDS.PAT_ID )
> AND ( EXT_PAT_IDS.EXT_PAT_ID_TYPE = 2 )
> AND ( ENCOUNTERS.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PATIENT_DEMO.PAT_ID=ENCOUNTERS2.PAT_ID(+) )
> AND ( ENCOUNTERS2.START_DATE < ( ENCOUNTERS.START_DATE ) )
> AND (
> EXT_PAT_IDS.EXT_PAT_ID = '01216116'
> )
> GROUP BY
> ENCOUNTERS.START_DATE
>
>
> RESULTS B
>
> Encounter Date, Previous Encounter Date
>
> 7/31/2000, 6/13/2000
> 8/7/2000, 7/31/2000
> 8/14/2000, 8/7/2000
> 8/21/2000, 8/14/2000
> 9/5/2000, 8/21/2000
> 10/2/2000, 9/5/2000
> 1/24/2001, 10/2/2000
> 3/26/2001, 1/24/2001
> 4/23/2001, 3/26/2001
>
> LOOKING FOR AN ADDITIONAL RECORD THAT WOULD BE 6/13/2000, [NULL]
>
>
>
>
Received on Sat Sep 21 2002 - 04:02:51 CEST