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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: REVIEWED: group by week

RE: REVIEWED: group by week

From: Kirsh, Gary <gary.kirsh_at_gs.com>
Date: Tue, 5 Dec 2000 14:15:40 -0500
Message-Id: <10701.123705@fatcity.com> 





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_at_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_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_001_01C05EEF.C1A20770


Content-Type: text/html;


        charset="iso-8859-1"


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">


<META content="MSHTML 5.50.4207.2601" name=GENERATOR></HEAD>


<BODY>


<DIV><SPAN class=048191419-05122000><FONT face=Arial color=#0000ff size=2>How 
about something like this:</FONT></SPAN></DIV>
<DIV><SPAN class=048191419-05122000><FONT face=Arial color=#0000ff size=2><FONT 
face="Courier New" size=2></FONT></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=048191419-05122000><FONT face=Arial color=#0000ff size=2><FONT 
face="Courier New" size=2>select 


next_day(7*(&amp;week-1)+to_date('1-jan-&amp;year'),'SUNDAY') from 
dual</FONT></FONT></SPAN></DIV>


<DIV><SPAN class=048191419-05122000></SPAN>&nbsp;</DIV>
<DIV><SPAN class=048191419-05122000><FONT color=#0000ff size=2><FONT 
size=2><FONT face=Arial>Gary Kirsh</FONT></FONT></FONT></SPAN></DIV>
<DIV><SPAN class=048191419-05122000><FONT color=#0000ff size=2><FONT 
size=2><FONT face=Arial>Next Extent, Inc</FONT></DIV></FONT></FONT></SPAN>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
  <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 
  size=2>-----Original Message-----<BR><B>From:</B> andrey 
  [mailto:bronfin_at_visualtop.com]<BR><B>Sent:</B> Tuesday, December 05, 2000 1:33 
  PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> 
  REVIEWED: group by week<BR><BR></FONT></DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=076173218-05122000>Dear 
  list !</SPAN></FONT></DIV>


  <DIV><FONT face=Arial color=#0000ff size=2><SPAN 
  class=076173218-05122000></SPAN></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=076173218-05122000>Is 
  there a function which converts a week number into a date of the first day of 
  the week ?</SPAN></FONT></DIV>


  <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=076173218-05122000>For 
  example , this week is week number 49 in the year 2000 .</SPAN></FONT></DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN class=076173218-05122000>I 
  need a function that will give me '03-DEC-2000'&nbsp; ( last sunday ) as an 
  output .</SPAN></FONT></DIV>


  <DIV><FONT face=Arial color=#0000ff size=2><SPAN 
  class=076173218-05122000></SPAN></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN 
  class=076173218-05122000>Thanks a lot !</SPAN></FONT></DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN 
  class=076173218-05122000></SPAN></FONT>&nbsp;</DIV>
  <DIV><FONT face=Arial color=#0000ff size=2><SPAN 
  class=076173218-05122000>Andrey .</SPAN></FONT></DIV>
  <BLOCKQUOTE style="MARGIN-RIGHT: 0px">


    <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma 

    size=2>-----Original Message-----<BR><B>From:</B> andrey 
    [mailto:bronfin_at_visualtop.com]<BR><B>Sent:</B> Tue, December 05, 2000 8:23 
    PM<BR><B>To:</B> oralist_at_lists; ORACLE-L_at_IC.SUNYSB.EDU; 
    ORACLE-L_at_fatcity.com; oracledba_at_quickdoc.co.uk; 
    oracledba_at_lazydba.com<BR><B>Subject:</B> group by week<BR><BR></DIV></FONT>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>Dear list 

    !</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>I have a table 

    AAA with fields (registration_date , status ) .</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>I need a report 

    which will group the results by week .</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>I.e instead of 

    </SPAN></FONT></DIV>

    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000>&nbsp;&nbsp;&nbsp; select 
    to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), count(status) 


    </SPAN></FONT></DIV>

    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000>&nbsp;&nbsp;&nbsp; from AAA</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>&nbsp;&nbsp; 

    group by 


    to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>I need something 

    like </SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>&nbsp; 

    select&nbsp; first_date_in_the_week(REGISTRATION_DATE) , count(status) 


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000>&nbsp;&nbsp;&nbsp; from AAA</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>&nbsp;&nbsp; 

    group by&nbsp;week_of (&nbsp;</SPAN></FONT></SPAN></FONT><FONT face=Arial 
    size=2><SPAN class=798181618-05122000>&nbsp; REGISTRATION_DATE ) 
    ;</SPAN></FONT></DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN 



    class=798181618-05122000></SPAN></FONT>&nbsp;</DIV>


    <DIV><FONT face=Arial size=2><SPAN class=798181618-05122000>Any ideas 

Received on Tue Dec 05 2000 - 13:15:40 CST












Original text of this message













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