Date and calendar in oracle

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.
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what do you expect the output to be?
0
sdstuberCommented:
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
anumosesAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day
0
flow01Commented:
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
sdstuberCommented:
>>> 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
anumosesAuthor Commented:
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
sdstuberCommented:
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
anumosesAuthor Commented:
WHERE h_date >= DATE '2015-06-01')

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

parameter is date format.
0
sdstuberCommented:
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
anumosesAuthor Commented:
Thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.