Solved

Oracle Dates output

Posted on 2014-07-30
4
402 Views
Last Modified: 2014-07-30
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
Comment
Question by:bcarlis
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40230873
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
 
LVL 2

Author Comment

by:bcarlis
ID: 40230897
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
 
LVL 25

Expert Comment

by:chaau
ID: 40230915
Oh, yeah. My mistake. Use max(n)+7 for both columns
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40230916
You da man!
Thanks so much for your help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question