Solved

Date and calendar in oracle

Posted on 2014-10-23
12
398 Views
Last Modified: 2014-10-29
http://www.experts-exchange.com/Database/Oracle/Q_28316594.html

The above is closed.

CREATE TABLE TAB_DATA
(
  DAY_OF_MONTH             VARCHAR2(10 BYTE),
  AU                       VARCHAR2(30 BYTE),
  TP                       VARCHAR2(30 BYTE),
  BOTH                     VARCHAR2(30 BYTE),
  TOT                      VARCHAR2(30 BYTE),
  LCM                      VARCHAR2(30 BYTE),
  CURRENT_MONTH_FIRST_DAY  DATE,
  DAY_OF_WEEK              VARCHAR2(30 BYTE)
)


Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('1', '1/110', '0/0', '0/0', '1/110', 
    '8/392', TO_DATE('06/01/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('2', '5/156', '1/31', '0/0', '6/187', 
    '12/588', TO_DATE('06/02/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('3', '6/181', '0/0', '0/0', '6/181', 
    '12/588', TO_DATE('06/03/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('4', '1/39', '1/23', '2/55', '4/117', 
    '11/539', TO_DATE('06/04/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('5', '1/38', '0/0', '1/280', '2/318', 
    '8/392', TO_DATE('06/05/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('6', '5/175', '0/0', '2/89', '7/264', 
    '9/392', TO_DATE('06/06/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('7', '2/52', '1/16', '0/0', '3/68', 
    '5/245', TO_DATE('06/07/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('8', '3/63', '0/0', '0/0', '3/63', 
    '8/392', TO_DATE('06/08/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('9', '2/73', '2/51', '1/20', '5/144', 
    '12/588', TO_DATE('06/09/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('10', '3/116', '0/0', '1/20', '4/136', 
    '12/588', TO_DATE('06/10/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('11', '3/70', '0/0', '0/0', '3/70', 
    '11/539', TO_DATE('06/11/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('12', '1/27', '0/0', '0/0', '1/27', 
    '8/392', TO_DATE('06/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('13', '3/179', '2/47', '0/0', '5/226', 
    '9/441', TO_DATE('06/13/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('14', '0/0', '0/0', '2/50', '2/50', 
    '5/245', TO_DATE('06/14/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('15', '1/25', '0/0', '0/0', '1/25', 
    '8/392', TO_DATE('06/15/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('16', '1/29', '0/0', '2/55', '3/84', 
    '12/588', TO_DATE('06/16/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('20', '1/29', '1/29', '1/31', '3/89', 
    '9/441', TO_DATE('06/20/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('18', '3/90', '2/118', '0/0', '5/208', 
    '11/539', TO_DATE('06/18/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('19', '0/0', '2/46', '1/31', '3/77', 
    '8/392', TO_DATE('06/19/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Friday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('21', '2/61', '1/77', '0/0', '3/138', 
    '5/245', TO_DATE('06/21/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('22', '3/140', '1/27', '0/0', '4/167', 
    '8/392', TO_DATE('06/22/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('23', '2/74', '1/20', '1/20', '4/114', 
    '12/588', TO_DATE('06/23/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Tuesday  ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('24', '2/43', '0/0', '0/0', '2/43', 
    '12/588', TO_DATE('06/24/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Wednesday');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('25', '2/56', '0/0', '0/0', '2/56', 
    '11/539', TO_DATE('06/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Thursday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('29', '0/0', '1/39', '1/42', '2/81', 
    '8/392', TO_DATE('06/29/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Monday   ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('27', '2/120', '0/0', '0/0', '2/120', 
    '9/441', TO_DATE('06/27/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Saturday ');
Insert into TAB_DATA
   (DAY_OF_MONTH, AU, TP, BOTH, TOT, 
    LCM, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK)
 Values
   ('28', '0/0', '2/46', '0/0', '2/46', 
    '5/245', TO_DATE('06/28/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'Sunday   ');
COMMIT;

Open in new window


We find some missing dates and hence my calendar is missing days and dates.

Is there a way to still get DAY_OF_MONTH, CURRENT_MONTH_FIRST_DAY, DAY_OF_WEEK with other values being zeros?

Help is appreciated.
0
Comment
Question by:anumoses
[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
  • 6
  • 5
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400025
what do you expect the output to be?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400044
Do you just need NVL on your results?

nvl(au,0) au,
nvl(tp,0) tp,
nvl(both,0) both,
nvl(tot,0) tot,
nvl(lcm,0) lcm,
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400050
For 17th  June 2015 I need as follows

17   0/0  0/0  0/0  0/0  0/0  6/17/2015   Wednesday

and also for any missing dates
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 74

Accepted Solution

by:
sdstuber earned 375 total points
ID: 40400121
WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd-mon-yyyy') startdate FROM DUAL),
     t
     AS (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
               FROM s1
         CONNECT BY LEVEL <= 999)
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date


I left the current_month_first_day on, based on original query, your output doesn't show it, but it's null, so I couldn't tell if it was missing or simply not visible
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400146
I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 125 total points
ID: 40400163
change the nvl result value,  show  t.h_date instead of a.current_month_first_day and use another format for day of week

with s1
as
(select to_date('01/03/2014','dd/mm/yyyy') startdate,  '0/0' null_value from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '1' then startdate
       else
        trunc(startdate,'day')
       end
       + (level - 1) h_date
      from s1
      connect by level <= 999
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'Dy') day_of_week ,
   nvl(au,s1.null_value) au ,
   nvl(tp,s1.null_value) tp,
   nvl(both,s1.null_value) both,
   nvl(tot,s1.null_value) tot,
   nvl(lcm,s1.null_value) lcm,
   t.h_date h_date,
   to_char(t.h_date,'Day') day_of_week2
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,3)
order by t.h_date
/
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40400205
>>> I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day


that's what I mentioned above.  Your expected results didn't include it.
but -  it's simply the generated h_date (whether populated or not, due to the join)

and, based on your expected results I was displaying that as the 2nd to last column already.

but, it's easy enough to return it twice


WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd/mon/yyyy') startdate FROM DUAL),
     t
     AS (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
               FROM s1
         CONNECT BY LEVEL <= 999)
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         h_date current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date


and, if you want to make sure it's formatted as a string dd/mm/yyyy then use to_char on it like I did on the h_date column above it
0
 
LVL 6

Author Comment

by:anumoses
ID: 40400270
with s1
as
(select to_date('01/06/2015','dd/mm/yyyy') startdate,  '0/0' null_value from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '1' then startdate
       else
        trunc(startdate,'day')
       end
       + (level - 1) h_date
      from s1
      connect by level <= 999
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'Dy') day_of_week ,
   nvl(au,s1.null_value) au ,
   nvl(tp,s1.null_value) tp,
   nvl(both,s1.null_value) both,
   nvl(tot,s1.null_value) tot,
   nvl(lcm,s1.null_value) lcm,
   t.h_date h_date,
   to_char(t.h_date,'Day') day_of_week2
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,3)
order by t.h_date


Is there a way to limit to the month? (June 2015)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 375 total points
ID: 40400299
yes, you can limit to a single month, just adjust the "t" query


WITH s1 AS (SELECT TO_DATE('01-mar-2014', 'dd/mon/yyyy') startdate FROM DUAL),
     t
     AS (SELECT *
           FROM (    SELECT NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) h_date
                       FROM s1
                 CONNECT BY NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) <= DATE '2015-06-30')
          WHERE h_date >= DATE '2015-06-01')
  SELECT TO_CHAR(t.h_date, 'dd') day_of_month,
         NVL(au, '0/0') au,
         NVL(tp, '0/0') tp,
         NVL(both, '0/0') both,
         NVL(tot, '0/0') tot,
         NVL(lcm, '0/0') lcm,
         TO_CHAR(t.h_date, 'mm/dd/yyyy') h_date,
         TO_CHAR(t.h_date, 'fmDay') day_of_week,
         h_date current_month_first_day
    FROM tab_data a, t
   WHERE a.current_month_first_day(+) = t.h_date
ORDER BY t.h_date
0
 
LVL 6

Author Comment

by:anumoses
ID: 40409161
WHERE h_date >= DATE '2015-06-01')

Can the date be a parameter where the user enters a date?

parameter is date format.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 375 total points
ID: 40409186
yes

use this if your parameter is a DATE type
WHERE h_date >= :your_parameter


use this if your parameter is text, change the format to whatever matches your text
WHERE h_date >= to_date(:your_parameter,'yyyy-mm-dd hh24:mi:ss')


note, there are 2 dates in that query

CONNECT BY NEXT_DAY(startdate - 7, 'Sun') + (LEVEL - 1) <= DATE '2015-06-30')

you'll probably want to make a parameter in the same type for the upper bound condition too
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40410615
Thanks for the help
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

691 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