BILL Carlisle
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
Thank you very much,
Bill
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
Thank you very much,
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, yeah. My mistake. Use max(n)+7 for both columns
ASKER
You da man!
Thanks so much for your help!
Thanks so much for your help!
ASKER
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