Re: date format and nesting

And I should read your question better. I thought you wanted the maximum hours worked in which case you could have done

where a.activ_hours = (select max(activ_hours) from activity)

however you want maximum hours per day. I have a solution for that below using an inline view.

SQL> create table t1(name varchar2(50),hours number,date_worked date)   2 tablespace users;

Table created.

SQL> insert into t1 values('Nial',10,sysdate);

1 row created.

SQL> insert into t1 values('Nial',12,sysdate+1);

1 row created.

SQL> insert into t1 values('Louis',16,sysdate);

1 row created.

SQL> insert into t1 values('Louis',0,sysdate+1);

1 row created.

SQL> commit;

Commit complete.
<snip typos!>
SQL> ed
Wrote file afiedt.buf

  1 select name,dt,hrs
  2 from t1,(
  3 select trunc(date_worked) dt,max(hours) hrs   4 from t1
  5 group by trunc(date_worked)) t2
  6 where trunc(t1.date_worked)=t2.dt
  7* and t1.hours=t2.hrs
SQL> /

NAME                                               DT               HRS
-------------------------------------------------- --------- ----------
Louis                                              17-APR-02         16
Nial                                               18-APR-02         12

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

"Louis Dumont" <dumontls_at_dumontls.com> wrote in message
news:mTUu8.14096$o66.45815_at_news-server.bigpond.net.au...


> PS i am using Oracle 8i 1.7 Lite

>

> "Louis Dumont" <dumontls_at_dumontls.com> wrote in message

> news:qRUu8.14087$o66.45730_at_news-server.bigpond.net.au...

> > I realise i have to put another where clause for works hours. I have

tried


> > evrything I possibly know, anyway  the project was due at on the 15th

and


> i

> > have submitted the the work. Ill know the answer soon enough and of

course


> > ill then realise where i made the mistake and kick my own but,

> >

> > but $50 hey come on.

> >

> > Louis

> >

> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in

message


> > news:3cbbda6d$0$236$ed9e5944_at_reading.news.pipex.net...

> > > that should read where clause for hours.

> > >

> > >

> > > --

> > > Niall Litchfield

> > > Oracle DBA

> > > Audit Commission UK

> > > *****************************************

> > > Please include version and platform

> > > and SQL where applicable

> > > It makes life easier and increases the

> > > likelihood of a good answer

> > >

> > > ******************************************

> > > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message

> > > news:3cbb35f7$0$230$cc9e4d1f_at_news.dial.pipex.com...

> > > > you don't have a where clause so you won't have any conditions,

> to_char

> > > > converts other formats (number/date etc) to character in the format

> > given

> > > by

> > > > the format mask. Further homework help is chargeable at 50 dollars

per


> > > hour

> > > > of students time.

> > > >

> > > > --

> > > > Niall Litchfield

> > > > "Louis Dumont" <dumontls_at_dumontls.com> wrote in message

> > > > news:4HQt8.2442$o66.5607_at_news-server.bigpond.net.au...

> > > > > I have the following statements and am finding difficulty doing 2

> > things

> > > > >

> > > > > formatting the date to 22-March-2002 and producing only the

highest


> > > hours

> > > > > worked for each day, any ideas here please

> > > > >

> > > > > SELECT TO_CHAR((N.PERS_NAME) || ' a ' || (P.POSN_NAME) || ' worked

'


> > ||

> > > > > A.ACTIV_HOURS || ' hours in ' || (U.UNIT_NAME) || ' on ' ||

> > > > (A.ACTIV_DATE))

> > > > > 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

> > > > > ORDER BY A.ACTIV_DATE

> > > > >

> > > > > Which gives me the following result

> > > > >

> > > > >

> > > >

> > >

> >

>

TO_CHAR((N.PERS_NAME)||'A'||(P.POSN_NAME)||'WORKED'||A.ACTIV_HOURS||'HOURSIN


> > > > > '||(

> > > >

> > >

> >

>

> --------------------------------------------------------------------------

> > > > --

> > > > > ----

> > > > > LEAR a LECTUER A worked 5 hours in I.S. 1 on 22-MAR-02

> > > > > EVERETT a TUTOR worked 5 hours in I.S. 1 on 22-MAR-02

> > > > > MENG a LECTUER B worked 4 hours in LAW 1 on 22-MAR-02

> > > > > FOWLER a LECTUER A worked 5 hours in LAW 1 on 22-MAR-02

> > > > > KENNETT a LECTUER A worked 4 hours in LAW 1 on 22-MAR-02

RICHARDSON


> a

> > > > > LECTUER B worked 4 hours in MANAGEMENT 1 on 22-MAR-02 HEUNG a

> LECTUER

> > A

> > > > > worked 6 hours in MANAGEMENT 1 on 22-MAR-02 SMITH a LECTUER A

worked


> 3

> > > > hours

> > > > > in ACCOUNTING 1 on 22-MAR-02 DAVILLIA a TUTOR worked 5 hours in

> > > ACCOUNTING

> > > > 1

> > > > > on 22-MAR-02

> > > > >

> > > > > However I need to only output the person who wrks the most hrs for

> > each

> > > > day,

> > > > > and have tried putting Max(a.activ_hours) in the select statement,

> it

> > > > doesn'

> > > > > t work.

> > > > >

> > > > > Any help here

> > > > >

> > > > > Also for the date I have to output in 22_march 2002 format and

tried


> > > using

> > > > > Fm(a.activ_date, 'DD-Month-YYYY') alos tried fmFormat(...) and

> > > > To_Date(...)

> > > > > it comes up with an error.

> > > > >

> > > > > Ideas pls

> > > > >

> > > > >

> > > > >

> > > >

> > > >

> > >

> > >

> >

> >

>

>