Solved

Oracle Dates output

Posted on 2014-07-30
4
378 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 24

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 24

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now