Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help

Help

From: Louis Dumont <dumontls_at_dumontls.com>
Date: Sat, 20 Apr 2002 08:34:51 GMT
Message-ID: <LK9w8.28503$o66.89058@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 Sat Apr 20 2002 - 03:34:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US