Re: Help

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 20 Apr 2002 12:40:18 +0200
Message-ID: <uc2hi3d0c8kgff@corp.supernews.com>

"Louis Dumont" <dumontls_at_dumontls.com> wrote in message news:LK9w8.28503$o66.89058_at_news-server.bigpond.net.au...
> The following SQL outputs the result that follows:
>
> What I really need to output is the person who worked the highest hours
i.e
> max(activ_hours) in each day. I have tried every which way and cannot do
it
> can somebody please advise how. the result shud only have 5 lines, one for
> each day from 18-22 March. tables Im working with are attached
>
> SQL> SELECT N.PERS_NAME || ' a ' || P.POSN_NAME || ' in ' || U.UNIT_NAME
||'
> worked ' || max(A.ACTIV
> _HOURS) || ' hours on ' || TO_CHAR(A.ACTIV_DATE,'fm(DD-Month-YYYY)')
> 2 FROM POSITION P,PERSONNEL N, ACTIVITY A, UNIT U
> 3 WHERE P.POSN_ID=N.POSN_ID
> 4 AND N.PERS_ID=A.PERS_ID
> 5 AND A.UNIT_ID=U.UNIT_ID
> 6 GROUP BY N.PERS_NAME, P.POSN_NAME, U.UNIT_NAME, A.ACTIV_HOURS,
> A.ACTIV_DATE
> 7 ORDER BY A.ACTIV_DATE;
>
>

SELECT N.PERS_NAME

              || ' a '
              || P.POSN_NAME
              || ' in '
              || U.UNIT_NAME
              ||' worked '
              ||A.ACTIV_HOURS
              || ' hours on '
             || TO_CHAR(A.ACTIV_DATE,'fm(DD-Month-YYYY)')
 FROM POSITION P
            ,PERSONNEL N
            , ACTIVITY A
             , UNIT U
 WHERE P.POSN_ID=N.POSN_ID
     AND N.PERS_ID=A.PERS_ID

    AND A.UNIT_ID=U.UNIT_ID
    and a. activ_hours =

          (select max(activ_hours)
           from activity a1
           where trunc(a1.activ_date) = trunc(a.activ_date)
 order BY
              A.ACTIV_DATE
             , N.PERS_NAME
              , P.POSN_NAME
              , U.UNIT_NAME;

This definitely smells like homework, if so, please provide the e-mail address of your teacher, so we can send it directly.

--
Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Sat Apr 20 2002 - 05:40:18 CDT