• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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
0
bcarlis
Asked:
bcarlis
  • 2
  • 2
1 Solution
 
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
 
bcarlisAuthor 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
 
bcarlisAuthor Commented:
You da man!
Thanks so much for your help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now