Solved

Date and calendar in oracle

Posted on 2014-10-23
12
364 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
  • 6
  • 5
12 Comments
 
LVL 73

Expert Comment

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

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Char delimited error 5 30
percentage remaining 6 40
dates - loop 12 41
SQL Retrieve Values 4 43
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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, 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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

757 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

21 Experts available now in Live!

Get 1:1 Help Now