We help IT Professionals succeed at work.

oracle query display data

anumoses
anumoses asked
on
411 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
Comment
Watch Question

Author

Commented:
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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

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

Author

Commented:
This is good. But how will I add to my existing query?

Author

Commented:
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?
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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
/

Author

Commented:
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
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
Don't you need the empty days after 30-apr-2014 ?

Author

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

Author

Commented:
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.
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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
/

Author

Commented:
How can I stop from the week starting with saturday? Everything looks good
flow01IT-specialist
CERTIFIED EXPERT

Commented:
"How can I stop from the week starting with saturday?
I don't understand.  What does not look good ?

Author

Commented:
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
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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 ?

Author

Commented:
Yes I get a 3
IT-specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
this worked. starts with a sunday
flow01IT-specialist
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
Thanks though

Author

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

Author

Commented:
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?
flow01IT-specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks a lot. That helped

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.