Link to home
Start Free TrialLog in
Avatar of BILL Carlisle
BILL CarlisleFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BILL Carlisle

ASKER

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
Oh, yeah. My mistake. Use max(n)+7 for both columns
You da man!
Thanks so much for your help!