Week # Expression 1

[borisbe]

I'm using a query manager but I can create oracle expressions so I'm hoping this can be done. I hope I can explain what I need so that it makes sense. I have a start_date and end_date field. Is there anyway to have it break out per week# in the calendar year so:

ID Start Date End Date
1 04/01/2009 04/13/2009
2 04/13/2009 04/13/2009
3 05/04/2009 05/07/2009


===========
Results needed

ID Week# Date Range

1 14 03/29/2009 to 04/04/2009
1 15 04/05/2009 to 04/11/2009
1 16 04/12/2009 to 04/18/2009
2 16 04/12/2009 to 04/18/2009
3 20 05/03/2009 to 05/09/2009

Appreciate any help.

 

[SantaMufasa]

Boris,

We can certainly help you achieve what you want, but first, we need to understand how you arrive at "14" for the week that covers dates "03/29/2009 to 04/04/2009". Could you please post SQL code that displays "14" for those inclusive dates?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[borisbe]

I just looked at a calendar and counted the weeks to see which week # start date 04/01/2009 was in and end date 04/13/2009 was in.

 

[SantaMufasa]

The reason that I ask, Boris, is because neither of Oracle's built-in "Week" functions correlate to your "counted" week numbers:

col w heading "Standard Week Number for|04/01/2009"
col x heading "ISO Week Number for |04/01/2009"
col y heading "Standard Week Number for|04/13/2009"
col z heading "ISO Week Number for |04/13/2009"
select to_char(to_date('04/01/2009','mm/dd/yyyy'),'"Week": ww. Dy, mm/dd/yyyy')w
      ,to_char(to_date('04/01/2009','mm/dd/yyyy'),'"Week": iw. Dy, mm/dd/yyyy')x
      ,to_char(to_date('04/13/2009','mm/dd/yyyy'),'"Week": ww. Dy, mm/dd/yyyy')y
      ,to_char(to_date('04/13/2009','mm/dd/yyyy'),'"Week": iw. Dy, mm/dd/yyyy')z
from dual
/

Standard Week Number for  ISO Week Number for       Standard Week Number for  ISO Week Number for
04/01/2009                04/01/2009                04/13/2009                04/13/2009
------------------------- ------------------------- ------------------------- -------------------------
Week: 13. Wed, 04/01/2009 Week: 14. Wed, 04/01/2009 Week: 15. Mon, 04/13/2009 Week: 16. Mon, 04/13/2009

So, do you want us to use Standard or ISO week numbering to produce your output?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, Boris...more correctly I should have said that your date ranges (e.g., "03/29/2009 to 04/04/2009") don't match either of Oracle's date ranges for "Week 14". As you see from my query, above, the sample dates that you posted ("04/01/2009" and "04/13/2009") do fall within Oracle's ISO Weeks that you listed for your output...the week start and end dates are what don't match up.

So, if you are willing to adjust your Week Start and End Dates to match Oracle's ISO Week Start and End Dates, then we can proceed.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[borisbe]

Thanks Dave. I'm willing to try whatever you think would be best. I don't know if this matters but in the query manager when I select the date field and look at the sql statement that it automatically creates, it has "TO_CHAR(START_DATE,'YYYY-MM-DD') and the results show as MM/DD/YYYY. Also, this query manager won't let me have "from" in the expression so if "from" is required then thanks for trying to help me. I really appreciate it.

 

[SantaMufasa]

...the sql statement that it automatically creates...has "TO_CHAR(START_DATE,'YYYY-MM-DD') and the results show as MM/DD/YYYY

I can't explain that behavior...What is the query manager you are using?...Do you have access to SQL*Plus?

...this query manager won't let me have "from" in the expression...

Can you please give an example of what is not allowed under that restriction?

In any case, here is code that does what you want, which I ran under the only SQL environment that I have available (SQL*Plus). First, I list your sample data, followed by code and results that you specified:

select * from boris;

 ID START_DAT END_DATE
--- --------- ---------
  1 01-APR-09 13-APR-09
  2 13-APR-09 13-APR-09
  3 04-MAY-09 07-MAY-09

select id
      ,to_char(start_date,'mm/dd/yyyy')||'-'||to_char(end_date,'mm/dd/yyyy') w
      ,wks.wk
      ,to_char(wk_beg,'mm/dd/yyyy')||' to '||to_char(wk_end,'mm/dd/yyyy') x
  from (select wk,min(dt) wk_beg, max(dt) wk_end
          from (select to_date('01/01/2009','mm/dd/yyyy')+dy-1 dt
                      ,to_char(to_date('01/01/2009','mm/dd/yyyy')+dy-1,'iw')iw
                  from (select rownum dy from all_tab_columns
                         where rownum <= 365
                       )
               )
              ,(select rownum wk from all_tab_columns
                 where rownum <= 53
               )
         where wk = iw
         group by wk
        ) wks
      ,boris
 where wk_beg between start_date and end_date
    or wk_end between start_date and end_date
/

 ID ID Dates                   Week# Date Range
--- --------------------- ---------- ------------------------
  1 04/01/2009-04/13/2009         14 03/30/2009 to 04/05/2009
  1 04/01/2009-04/13/2009         15 04/06/2009 to 04/12/2009
  1 04/01/2009-04/13/2009         16 04/13/2009 to 04/19/2009
  2 04/13/2009-04/13/2009         16 04/13/2009 to 04/19/2009
  3 05/04/2009-05/07/2009         19 05/04/2009 to 05/10/2009

My reference to ALL_TAB_COLUMNS was because, in Oracle 8, I need some table that has plenty of rows to generate numbers (ROWNUM) that I need to correspond to days and weeks of the year. (Oracle 10g has a simpler method to generate numbers...See "select level from dual connect by level <= <your target number>".)

To understand what I have done in the code, above, begin "execution" from the innermost parentheses and see if you can create COMMENTS that explain the purpose of the code, for each piece of code. If you have troubles figuring out the purpose of any piece of code, please feel free to post your questions here.

Let us know your thoughts about this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[borisbe]

I have mysql where I can create tables and statements so I might dump the data from the query manager to a mysql table since I'm really limited in the manager. They have me on something else now but as soon as I can, I want to see if I can get your statement working. Thanks for helping me. I'll post an update as soon as I can.