Solved

oracle query display data

Posted on 2013-11-13
24
389 Views
Last Modified: 2013-11-15
I am trying to display this data in the form of a calendar. I got everythingworking.
But need help in display data starting from sunday being the first day of the week.

Sunday   Monday   Tuesday  Wednesday  Thursday  Friday  Saturday

In this case April 2014 data first day of the month is Tuesday. So I want Sunday, monday to be null and then the data display starts from Tuesday. Any help is appreciated.
I have only attached a screen print of few lines from the calendar display. If you see the screen print, in that I want the data display from the 3rd position.
data-load.txt
data-display.xls
calendar.JPG
calendar.JPG
0
Comment
Question by:anumoses
  • 14
  • 9
24 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 39644744
SELECT day_of_month, day_of_week,au, tp, both, tot, lcm
   from tab_data a,
     (SELECT to_date('01-apr-2014','DD-MON-YYYY')+rownum-1 h_date
      FROM all_tables
      WHERE to_date('01-apr-2014','DD-MON-YYYY')+rownum-1 <= to_date('30-apr-2014','DD-MON-YYYY')
      ) t
   where a.current_month_first_day(+) = t.h_date
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39645075
This query should give you the nulls to fill in the beginning of the week and then the full month.  I think that should be all you need.

SELECT NULL 
FROM   dual 
CONNECT BY Next_day(To_date('04012014', 'mmddyyyy')-6, 'sunday') 
           + LEVEL - 1 < To_date('04012014', 'mmddyyyy') 
UNION ALL 
SELECT To_date('04012014', 'mmddyyyy') + LEVEL - 1 
FROM   dual 
CONNECT BY To_date('04012014', 'mmddyyyy') + LEVEL - 1 <= 
           To_date('04302014', 'mmddyyyy'); 

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 39645116
This is good. But how will I add to my existing query?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645418
SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM   dual
CONNECT BY Next_day(To_date('04012014', 'mmddyyyy')-6, 'sunday')
           + LEVEL - 1 < To_date('04012014', 'mmddyyyy')
UNION ALL
SELECT To_date('04012014', 'mmddyyyy') + LEVEL - 1 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM   dual
CONNECT BY To_date('04012014', 'mmddyyyy') + LEVEL - 1 <=
           To_date('04302014', 'mmddyyyy')
UNION ALL              
SELECT NULL,day_of_month, day_of_week,au, tp, both, tot, lcm
   from tab_data a,
     (SELECT to_date('01-apr-2014','DD-MON-YYYY')+rownum-1 h_date
      FROM all_tables
      WHERE to_date('01-apr-2014','DD-MON-YYYY')+rownum-1 <= to_date('30-apr-2014','DD-MON-YYYY')
      ) t
   where a.current_month_first_day(+) = t.h_date      

Is there a way to get all the data in one line instead of showing nulls where there is data?
0
 
LVL 20

Expert Comment

by:flow01
ID: 39645787
check if  this is what you need ?

SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'day') day_of_week , au, tp, both, tot, lcm
   from tab_data a,
     (select trunc(to_date('01-04-2014'),'day') -1 + (level - 1) h_date
        -- trunc 'day' returns the first day of the week : monday
        -- subtract 1 to get the sunday of that week
        -- add (level  - 1) to start from that sunday itself = level 1
      from dual
      connect by level <= 35  -- leafs 1 empty week when a regular february starts on sunday
      ) t
   where a.current_month_first_day(+) = t.h_date
order by t.h_date
/
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645816
Yes this is what I need. Can I have and end date ie between 01-apr-2014 and 30-apr-2014. This would be great
0
 
LVL 20

Expert Comment

by:flow01
ID: 39645817
When the first day of the month is a sunday , you will get a empty week first and to few dates to fit a whole 30 days month.  Probably in need of a CASE brach.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39645821
Don't you need the empty days after 30-apr-2014 ?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645846
I am doing this calendar by month
so I have 2 parameters start_date and end_date. For me week starts from sunday. I have built a calendar form with all the data and now the users want to print this calendar in a report.

Sunday   Monday  Tuesday  Wednesday  Thursday  Friday  Saturday
                                2/20        6/190            8/177    2/25      2/46
  4/136     4/42         3/49

This is how I want to display values
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645884
I am able to display the values as I need in the report. But my week has to start from sunday instead of a saturday from your query and the end date. Then I will be good to complete this report. Thanks in advance.
0
 
LVL 20

Expert Comment

by:flow01
ID: 39645895
If  you are on a oracle version that can use the with option
(both with start on sunday and no days after the month)

with s1
as
(select to_date('01-04-2014','dd/mm/yyyy') startdate from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '7' then startdate  -- if sunday than no shift needed
       else
        trunc(startdate,'day') -1
       end
       + (level - 1) h_date
        -- trunc 'day' returns the first day of the week : monday
        -- subtract 1 to get the sunday of that week
        -- add (level  - 1) to start from that sunday itself = level 1
      from s1
      connect by level <= 35  -- leafs 1 empty week when a regular february starts on sunday
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'day') day_of_week , au, tp, both, tot, lcm
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,1)  --  end with the last day of the month
order by t.h_date
/
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645904
How can I stop from the week starting with saturday? Everything looks good
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 20

Expert Comment

by:flow01
ID: 39645921
"How can I stop from the week starting with saturday?
I don't understand.  What does not look good ?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645930
The query for this date range starts with saturday 29th March 2014. But my week has to start with a sunday for any given month. That is my request.
data.JPG
0
 
LVL 20

Expert Comment

by:flow01
ID: 39645957
Your week data setting is probably different :

with s1
as
(select to_date('01-04-2014','dd-mm-yyyy') startdate from dual)
select startdate, to_char(startdate,'d')  FROM S1
/

I  get :

STARTDAT T
-------- -
01-04-14 2

I assume you get a 3. Is that right ?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645964
Yes I get a 3
0
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39645975
change the query to
(i have not found the setting to change so I can't test for myself)

sunday check  to_char(startdate,'d')  changes from  '7'  to '1'  
trunc(startdate,'day')  gets for you the sunday so don't subtract another day


with s1
as
(select to_date('01-04-2014','dd/mm/yyyy') startdate from dual)
,
t
as
(
select case to_char(startdate,'d')
       when '1' then startdate  -- if sunday than no shift needed
       else
        trunc(startdate,'day')
       end
       + (level - 1) h_date
        -- trunc 'day' returns the first day of the week : monday
        -- subtract 1 to get the sunday of that week
        -- add (level  - 1) to start from that sunday itself = level 1
      from s1
      connect by level <= 35  -- leafs 1 empty week when a regular february starts on sunday
)
SELECT to_char(t.h_date,'dd') day_of_month, to_char(t.h_date,'day') day_of_week , au, tp, both, tot, lcm
   from tab_data a, t, s1
   where a.current_month_first_day(+) = t.h_date
   and t.h_date < add_months(startdate,1)  --  end with the last day of the month
order by t.h_date
/
0
 
LVL 6

Author Comment

by:anumoses
ID: 39645981
this worked. starts with a sunday
0
 
LVL 20

Expert Comment

by:flow01
ID: 39646013
The difference had to do with nls-settings
with first executing
DBMS_SESSION.SET_NLS ('NLS_TERRITORY','AMERICA');
I get a 3 to.

So a Dutch week starts on monday and an American week at sunday.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39646081
Thanks though
0
 
LVL 6

Author Comment

by:anumoses
ID: 39647687
For the month of march 2014 I am getting data display only until 29th march. Table has the data. Can you help me in the query? Attaching the data.

Thanks,
table-data.txt
0
 
LVL 6

Author Comment

by:anumoses
ID: 39647743
I changed this

 connect by level <= 35  -- leafs 1 empty week when a regular february starts on sunday

to  connect by level <= 37  -- leafs 1 empty week when a regular february starts on sunday

Will that help?
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 500 total points
ID: 39648793
I think that will help and also be the needed maximum.

My fault : I thought a month could cover either 4 or 5 weeks.
So I limited the level to 5*7 = 35
But it can cover 4,5 or 6 weeks.

Smallest scope is when there is a month of 28 days starting  on sunday :
you need 28 days then and there are 4 weeks
Highest scope is when there is a month of 31 days starting on saterday :
you will need  6 extra days and the are 6 weeks.
So 6 + 31 = 37 should fit al cases.

Because of the later added condition to only select dates less then the first day of the next month the comment is not correct any more.  The select doesnt leave an empty week. (but in depending on the way you fill your calendar there will be in the output)
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39650994
Thanks a lot. That helped
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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

13 Experts available now in Live!

Get 1:1 Help Now