Home » SQL & PL/SQL » SQL & PL/SQL » dynamic list of dates (10g)
dynamic list of dates [message #313118] Thu, 10 April 2008 15:29 Go to next message
ericdp
Messages: 4
Registered: May 2006
Location: Bellevue, WA
Junior Member

I would like to return a list of ISO weeks between a range:
SELECT MIN (dt) AS mi, MAX (dt) AS ma FROM tbl;

Currently this returns 17-Jan-2008 and 10-Apr-2008



Using this example as a starting point. It will dynamically create a list of dates for the month of Oct 2007.
select 
    to_date ( '10 2007','MM YYYY' ) - 1 + level as DateRange
  from    
    dual
  where
    ( to_date ( '10 2007','MM YYYY' ) - 1 + level ) <= last_day ( to_date ( '10 2007','MM YYYY' ) )
  connect by 
    level <= 31


But I can't seem to get this to work. What I'd like to do is eventually return a list of ISO weeks
TO_CHAR ( dt, 'IYYY-IW') AS iso_week

between the above dates. E.g.

2008-07
2008-08
2008-09
...
2008-15


I tried putting my min/max query in place of DUAL but it would only return one row. I could not get it to continue on to the max date. I am guessing it could not continue like it could with the dual table. My min/max only returned one row, so it only gave me one row. How can I get the connect by to start with my min date and continue on to my max date?

This will be part of a report that will run weekly, so the end date will be changing.

Any ideas?


Re: dynamic list of dates [message #313121 is a reply to message #313118] Thu, 10 April 2008 15:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: dynamic list of dates [message #313230 is a reply to message #313118] Fri, 11 April 2008 02:55 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
As Michel says, this is not an expert level question. You have (basically) date + LEVEL
Which will add one day on to the previous row's date. What you actually want to do is add 1 WEEK onto the previous row's date. (simple alteration of your arithmetic in the date generation.)
Previous Topic: filtering columns with NULL values
Next Topic: need help on writing query for a complex situation
Goto Forum:
  


Current Time: Sat Dec 10 13:07:58 CST 2016

Total time taken to generate the page: 0.09482 seconds