Oracle Dates output

Hi,
I am needing a SQL for a LOV that displays today, everyday until the second Sunday from today, then displays the rest of just the Sundays of the month, then displays just the first Sunday of each month for 12 months.
I've thrown a few ideas around but...

Here is an example of the output:
THURSDAY      5th Week of July      07/31/2014
FRIDAY      1th Week of August      08/01/2014
SATURDAY      1th Week of August      08/02/2014
SUNDAY      1th Week of August      08/03/2014
MONDAY      1th Week of August      08/04/2014
TUESDAY      1th Week of August      08/05/2014
WEDNESDAY      1th Week of August      08/06/2014
THURSDAY      1th Week of August      08/07/2014
FRIDAY      2th Week of August      08/08/2014
SATURDAY      2th Week of August      08/09/2014
SUNDAY      2th Week of August      08/10/2014
SUNDAY      3th Week of August      08/17/2014
SUNDAY      4th Week of August      08/24/2014
SUNDAY      5th Week of August      08/31/2014
SUNDAY      1th Week of September      09/07/2014
SUNDAY      1th Week of October      10/05/2014
SUNDAY      1th Week of November      11/02/2014

Here is an example of hardcoded values... I need it to be all dynamic. If today is Tuesday it would be the same output as above but have Tuesday and Wednesday also... plus the rest of the 12 months of Sundays
select * from (
select to_char(sysdate,'DAY'), to_char(sysdate,'W')||'th Week of July', sysdate from dual
union
select to_char(sysdate+1,'DAY'), to_char(sysdate+1,'W')||'th Week of August', sysdate+1 from dual
union
select to_char(sysdate+2,'DAY'), to_char(sysdate+2,'W')||'th Week of August', sysdate+2 from dual
union
select to_char(sysdate+3,'DAY'), to_char(sysdate+3,'W')||'th Week of August', sysdate+3 from dual
union
select to_char(sysdate+4,'DAY'), to_char(sysdate+4,'W')||'th Week of August', sysdate+4 from dual
union
select to_char(sysdate+5,'DAY'), to_char(sysdate+5,'W')||'th Week of August', sysdate+5 from dual
union
select to_char(sysdate+6,'DAY'), to_char(sysdate+6,'W')||'th Week of August', sysdate+6 from dual
union
select to_char(sysdate+7,'DAY'), to_char(sysdate+7,'W')||'th Week of August', sysdate+7 from dual
union
select to_char(sysdate+8,'DAY'), to_char(sysdate+8,'W')||'th Week of August', sysdate+8 from dual
union
select to_char(sysdate+9,'DAY'), to_char(sysdate+9,'W')||'th Week of August', sysdate+9 from dual
union
select to_char(sysdate+10,'DAY'), to_char(sysdate+10,'W')||'th Week of August', sysdate+10 from dual
union
select to_char(sysdate+17,'DAY'), to_char(sysdate+17,'W')||'th Week of August', sysdate+17 from dual
union
select to_char(sysdate+24,'DAY'), to_char(sysdate+24,'W')||'th Week of August', sysdate+24 from dual
union
select to_char(sysdate+31,'DAY'), to_char(sysdate+31,'W')||'th Week of August', sysdate+31 from dual
union
select to_char(sysdate+38,'DAY'), to_char(sysdate+38,'W')||'th Week of September', sysdate+38 from dual
union
select to_char(sysdate+66,'DAY'), to_char(sysdate+66,'W')||'th Week of October', sysdate+66 from dual
union
select to_char(sysdate+94,'DAY'), to_char(sysdate+94,'W')||'th Week of November', sysdate+94 from dual
) order by 3

Open in new window


Thank you very much,
Bill
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
It was tough. This dynamic SQL will give you the required result:
with requirement1 (n) as
(
  select 0 from dual
  UNION all
  select n + 1
  FROM requirement1 
  where TRUNC(sysdate) + n + 1 < CAST(next_day(sysdate+7, 'SUNDAY') as date)
),
requirement2 (n, orig) as
(
  select max(n), max(n) from requirement1
  UNION ALL
  select n + 7, orig
  FROM requirement2 
  where to_char(sysdate + n + 7, 'MM') =  to_char(sysdate + orig, 'MM')
),
requirement3 (n, m) as
(
  select TRUNC(NEXT_DAY(TRUNC(Add_months(SYSDATE + (select max(n) from requirement2), 1), 'MM'), to_char(to_date('7','J'),'Day')) - sysdate), 3 from dual
  union all
  select 
    TRUNC(NEXT_DAY(TRUNC(Add_months(SYSDATE, m), 'MM'), to_char(to_date('7','J'),'Day')) - sysdate),
    m + 1
  FROM requirement3
  where m < 12
),
dates as
(
  select TRUNC(sysdate) + n as dd from requirement1
  union all
  select TRUNC(sysdate) + n as dd  from requirement2
  union all
  select TRUNC(sysdate) + n as dd  from requirement3
)
select to_char(dd,'DAY'), to_char(dd,'W')||'th Week of ' || to_char(dd,'MONTH'), dd
from dates order by dd;

Open in new window

SQL Fiddle
Results:
| TO_CHAR(DD,'DAY') | TO_CHAR(DD,'W')||'THWEEKOF'||TO_CHAR(DD,'MONTH') |                               DD |
|-------------------|--------------------------------------------------|----------------------------------|
|         THURSDAY  |                            5th Week of JULY      |      July, 31 2014 00:00:00+0000 |
|         FRIDAY    |                            1th Week of AUGUST    |    August, 01 2014 00:00:00+0000 |
|         SATURDAY  |                            1th Week of AUGUST    |    August, 02 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of AUGUST    |    August, 03 2014 00:00:00+0000 |
|         MONDAY    |                            1th Week of AUGUST    |    August, 04 2014 00:00:00+0000 |
|         TUESDAY   |                            1th Week of AUGUST    |    August, 05 2014 00:00:00+0000 |
|         WEDNESDAY |                            1th Week of AUGUST    |    August, 06 2014 00:00:00+0000 |
|         THURSDAY  |                            1th Week of AUGUST    |    August, 07 2014 00:00:00+0000 |
|         FRIDAY    |                            2th Week of AUGUST    |    August, 08 2014 00:00:00+0000 |
|         SATURDAY  |                            2th Week of AUGUST    |    August, 09 2014 00:00:00+0000 |
|         SUNDAY    |                            2th Week of AUGUST    |    August, 10 2014 00:00:00+0000 |
|         SUNDAY    |                            2th Week of AUGUST    |    August, 10 2014 00:00:00+0000 |
|         SUNDAY    |                            3th Week of AUGUST    |    August, 17 2014 00:00:00+0000 |
|         SUNDAY    |                            4th Week of AUGUST    |    August, 24 2014 00:00:00+0000 |
|         SUNDAY    |                            5th Week of AUGUST    |    August, 31 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of SEPTEMBER | September, 07 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of OCTOBER   |   October, 05 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of NOVEMBER  |  November, 02 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of DECEMBER  |  December, 07 2014 00:00:00+0000 |
|         SUNDAY    |                            1th Week of JANUARY   |   January, 04 2015 00:00:00+0000 |
|         SUNDAY    |                            1th Week of FEBRUARY  |  February, 01 2015 00:00:00+0000 |
|         SUNDAY    |                            1th Week of MARCH     |     March, 01 2015 00:00:00+0000 |
|         SUNDAY    |                            1th Week of APRIL     |     April, 05 2015 00:00:00+0000 |
|         SUNDAY    |                            1th Week of MAY       |       May, 03 2015 00:00:00+0000 |
|         SUNDAY    |                            1th Week of JUNE      |      June, 07 2015 00:00:00+0000 |

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BILL CarlisleAPEX DeveloperAuthor Commented:
That is awesome!
again, awesome!
It has two 8/10 but I could not figure out how to not include one in req2 or reduce it from req3
SUNDAY      2th Week of AUGUST      08/10/2014
SUNDAY      2th Week of AUGUST      08/10/2014
0
chaauCommented:
Oh, yeah. My mistake. Use max(n)+7 for both columns
0
BILL CarlisleAPEX DeveloperAuthor Commented:
You da man!
Thanks so much for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.