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