Solved

Oracle Dates output

Posted on 2014-07-30
4
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: 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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

728 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