asked on

Date and calendar in oracle

https://www.experts-exchange.com/questions/28316594/Oracle-data-Calendar-display.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.

Sean Stuber

what do you expect the output to be?

Sean Stuber

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,

anumoses

ASKER

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

I was looking at 17th june 2015 data and it was missing data (6/17/2015) current_month_first_day

SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

anumoses

ASKER

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)

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

WHERE h_date >= DATE '2015-06-01')

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

parameter is date format.

SOLUTION

Sean Stuber

membership