Re: Help required.

I will use analytical functions and an embedded sql This link is helpful: http://www.nyoug.org/funct8i.pdf

"Louis Dumont" <dumontls_at_dumontls.com> wrote in message news:lL9w8.28505$o66.88767_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;
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> WONG a CASUAL in ACCOUNTING 1 worked 6 hours on (18-March-2002)
> CARIDI a CASUAL in I.S. 1 worked 6 hours on (18-March-2002)
> SMITH a LECTUER A in ACCOUNTING 1 worked 5 hours on (18-March-2002)
> TAN a LECTUER A in ACCOUNTING 1 worked 5 hours on (18-March-2002)
> LEAR a LECTUER A in I.S. 1 worked 5 hours on (18-March-2002)
> ROWLEY a LECTUER A in I.S. 1 worked 5 hours on (18-March-2002)
> FOWLER a LECTUER A in LAW 1 worked 5 hours on (18-March-2002)
> KENNETT a LECTUER A in LAW 1 worked 5 hours on (18-March-2002)
> MORLEY a LECTUER A in MANAGEMENT 1 worked 5 hours on (18-March-2002)
> MENSFORTH a LECTUER B in ACCOUNTING 1 worked 3 hours on (18-March-2002)
> RIVIS a LECTUER B in ACCOUNTING 1 worked 3 hours on (18-March-2002)
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> ROUSSEAU a LECTUER B in LAW 1 worked 3 hours on (18-March-2002)
> SINFIS a LECTUER B in MANAGEMENT 1 worked 3 hours on (18-March-2002)
> AUSTEN a LECTUER C in I.S. 1 worked 3 hours on (18-March-2002)
> STOMOSS a LECTUER C in MANAGEMENT 1 worked 2 hours on (18-March-2002)
> PIOTTO a HEAD OF SCHOOL in DATABASE DESIGN worked 5 hours on
 (19-March-2002)
> PIOTTO a HEAD OF SCHOOL in PROGRAMMING 1 worked 3 hours on (19-March-2002)
> LEAR a LECTUER A in PROGRAMMING 1 worked 5 hours on (19-March-2002)
> ROWLEY a LECTUER A in PROGRAMMING 1 worked 5 hours on (19-March-2002)
> MORLEY a LECTUER A in SYSTEMS DESIGN worked 3 hours on (19-March-2002)
> MENSFORTH a LECTUER B in ACCOUNTING SYSTEMS worked 4 hours on
> (19-March-2002)
> MACFEE a LECTUER B in SYSTEMS DESIGN worked 4 hours on (19-March-2002)
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> AUSTEN a LECTUER C in DATABASE DESIGN worked 3 hours on (19-March-2002)
> DAVILLIA a TUTOR in ACCOUNTING SYSTEMS worked 6 hours on (19-March-2002)
> EVERETT a TUTOR in DATABASE DESIGN worked 4 hours on (19-March-2002)
> EVERETT a TUTOR in SYSTEMS DESIGN worked 2 hours on (19-March-2002)
> WONG a CASUAL in ACCOUNTING 1 worked 6 hours on (20-March-2002)
> SMITH a LECTUER A in ACCOUNTING 1 worked 3 hours on (20-March-2002)
> TAN a LECTUER A in ACCOUNTING 1 worked 3 hours on (20-March-2002)
> ROWLEY a LECTUER A in I.S. 1 worked 4 hours on (20-March-2002)
> FOWLER a LECTUER A in LAW 1 worked 5 hours on (20-March-2002)
> MORLEY a LECTUER A in MANAGEMENT 1 worked 4 hours on (20-March-2002)
> RIVIS a LECTUER B in ACCOUNTING 1 worked 4 hours on (20-March-2002)
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> MACFEE a LECTUER B in I.S. 1 worked 3 hours on (20-March-2002)
> ROUSSEAU a LECTUER B in LAW 1 worked 3 hours on (20-March-2002)
> MENG a LECTUER B in LAW 1 worked 5 hours on (20-March-2002)
> RICHARDSON a LECTUER B in MANAGEMENT 1 worked 4 hours on (20-March-2002)
> SINFIS a LECTUER B in MANAGEMENT 1 worked 4 hours on (20-March-2002)
> JACKEL a LECTUER C in ACCOUNTING 1 worked 4 hours on (20-March-2002)
> WESTON a LECTUER C in LAW 1 worked 5 hours on (20-March-2002)
> DAVILLIA a TUTOR in ACCOUNTING 1 worked 5 hours on (20-March-2002)
> EVERETT a TUTOR in I.S. 1 worked 6 hours on (20-March-2002)
> SMITH a LECTUER A in AUDITING worked 4 hours on (21-March-2002)
> FOWLER a LECTUER A in FINANCE 1 worked 3 hours on (21-March-2002)
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> TAN a LECTUER A in TAX worked 5 hours on (21-March-2002)
> RIVIS a LECTUER B in FINANCE 1 worked 4 hours on (21-March-2002)
> RICHARDSON a LECTUER B in POLICY worked 3 hours on (21-March-2002)
> SINFIS a LECTUER B in POLICY worked 4 hours on (21-March-2002)
> STOMOSS a LECTUER C in POLICY worked 3 hours on (21-March-2002)
> JACKEL a LECTUER C in TAX worked 4 hours on (21-March-2002)
> DAVILLIA a TUTOR in AUDITING worked 5 hours on (21-March-2002)
> ROBBINS a CASUAL in I.S. 1 worked 6 hours on (22-March-2002)
> SMITH a LECTUER A in ACCOUNTING 1 worked 3 hours on (22-March-2002)
> LEAR a LECTUER A in I.S. 1 worked 5 hours on (22-March-2002)
> KENNETT a LECTUER A in LAW 1 worked 4 hours on (22-March-2002)
>
>

 N.PERS_NAME||'A'||P.POSN_NAME||'IN'||U.UNIT_NAME||'WORKED'||MAX(A.ACTIV_HOUR
> S)||
> --------------------------------------------------------------------------
 --
> ----
> FOWLER a LECTUER A in LAW 1 worked 5 hours on (22-March-2002)
> HEUNG a LECTUER A in MANAGEMENT 1 worked 6 hours on (22-March-2002)
> MENSFORTH a LECTUER B in ACCOUNTING 1 worked 2 hours on (22-March-2002)
> MENG a LECTUER B in LAW 1 worked 4 hours on (22-March-2002)
> RICHARDSON a LECTUER B in MANAGEMENT 1 worked 4 hours on (22-March-2002)
> SINFIS a LECTUER B in MANAGEMENT 1 worked 4 hours on (22-March-2002)
> CAMPBELL a LECTUER C in I.S. 1 worked 3 hours on (22-March-2002)
> DAVILLIA a TUTOR in ACCOUNTING 1 worked 5 hours on (22-March-2002)
> EVERETT a TUTOR in I.S. 1 worked 5 hours on (22-March-2002)
>
> 64 rows selected.
>
>
>
Received on Tue Apr 23 2002 - 17:12:34 CEST