This is a multi-part message in MIME format.
------=_NextPart_000_000D_01C05EFA.ED12EF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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_at_visualtop.com]
Sent: Tue, December 05, 2000 8:23 PM
To: oralist_at_lists; ORACLE-L_at_IC.SUNYSB.EDU; ORACLE-L_at_fatcity.com;
oracledba_at_quickdoc.co.uk; oracledba_at_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_000_000D_01C05EFA.ED12EF90
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>Dear=20
list !</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D076173218-05122000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>Is=20
there a function which converts a week number into a date of the first =
day of=20
the week ?</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>For=20
example , this week is week number 49 in the year 2000 =
.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>I need=20
a function that will give me '03-DEC-2000' ( last sunday ) as an =
output=20
.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D076173218-05122000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>Thanks=20
a lot !</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D076173218-05122000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D076173218-05122000>Andrey=20
.</SPAN></FONT></DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B> andrey=20
[mailto:bronfin_at_visualtop.com]<BR><B>Sent:</B> Tue, December 05, 2000 =
8:23=20
PM<BR><B>To:</B> oralist_at_lists; ORACLE-L_at_IC.SUNYSB.EDU; =
ORACLE-L_at_fatcity.com;=20
oracledba_at_quickdoc.co.uk; oracledba_at_lazydba.com<BR><B>Subject:</B> =
group by=20
week<BR><BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>Dear =
list=20
!</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
have a table AAA=20
with fields (registration_date , status ) .</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
need a report=20
which will group the results by week .</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I.e =
instead of=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> =20
select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), =
count(status)=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> =20
from AAA</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> group=20
by =
to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
need something=20
like </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> =20
select first_date_in_the_week(REGISTRATION_DATE) , count(status) =
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> =20
from AAA</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> group=20
by week_of ( </SPAN></FONT></SPAN></FONT><FONT face=3DArial=20
size=3D2><SPAN class=3D798181618-05122000> REGISTRATION_DATE )=20
;</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>Any =
Received on Tue Dec 05 2000 - 12:35:37 CST
