Message-Id: <10701.123705@fatcity.com> From: "Kirsh, Gary" Date: Tue, 5 Dec 2000 14:15:40 -0500 Subject: RE: REVIEWED: group by week This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C05EEF.C1A20770 Content-Type: text/plain; charset="iso-8859-1" How about something like this: select next_day(7*(&week-1)+to_date('1-jan-&year'),'SUNDAY') from dual Gary Kirsh Next Extent, Inc -----Original Message----- From: andrey [mailto:bronfin@visualtop.com] Sent: Tuesday, December 05, 2000 1:33 PM To: Multiple recipients of list ORACLE-L Subject: REVIEWED: group by week Dear list ! Is there a function which converts a week number into a date of the first day of the week ? For example , this week is week number 49 in the year 2000 . I need a function that will give me '03-DEC-2000' ( last sunday ) as an output . Thanks a lot ! Andrey . -----Original Message----- From: andrey [mailto:bronfin@visualtop.com] Sent: Tue, December 05, 2000 8:23 PM To: oralist@lists; ORACLE-L@IC.SUNYSB.EDU; ORACLE-L@fatcity.com; oracledba@quickdoc.co.uk; oracledba@lazydba.com Subject: group by week Dear list ! I have a table AAA with fields (registration_date , status ) . I need a report which will group the results by week . I.e instead of select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), count(status) from AAA group by to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')); I need something like select first_date_in_the_week(REGISTRATION_DATE) , count(status) from AAA group by week_of ( REGISTRATION_DATE ) ; Any ideas ? ------_=_NextPart_001_01C05EEF.C1A20770 Content-Type: text/html; charset="iso-8859-1"

How about something like this:

select next_day(7*(&week-1)+to_date('1-jan-&year'),'SUNDAY') from dual

Gary Kirsh

Next Extent, Inc

-----Original Message-----
From: andrey [mailto:bronfin@visualtop.com]
Sent: Tuesday, December 05, 2000 1:33 PM
To: Multiple recipients of list ORACLE-L
Subject: REVIEWED: group by week

Dear list !

Is there a function which converts a week number into a date of the first day of the week ?

For example , this week is week number 49 in the year 2000 .

I need a function that will give me '03-DEC-2000' ( last sunday ) as an output .

Thanks a lot !

Andrey .

-----Original Message-----
From: andrey [mailto:bronfin@visualtop.com]
Sent: Tue, December 05, 2000 8:23 PM
To: oralist@lists; ORACLE-L@IC.SUNYSB.EDU; ORACLE-L@fatcity.com; oracledba@quickdoc.co.uk; oracledba@lazydba.com
Subject: group by week

Dear list !

I have a table AAA with fields (registration_date , status ) .

I need a report which will group the results by week .

I.e instead of

    select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), count(status)

    from AAA

   group by to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));

I need something like

select first_date_in_the_week(REGISTRATION_DATE) , count(status)
    from AAA

   group by week_of (   REGISTRATION_DATE ) ;

Any ideas