anumoses
asked on
oracle query display data
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
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
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');
ASKER
This is good. But how will I add to my existing query?
ASKER
SELECT NULL,NULL,NULL,NULL,NULL,N ULL,NULL,N ULL
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?
FROM dual
CONNECT BY Next_day(To_date('04012014
+ LEVEL - 1 < To_date('04012014', 'mmddyyyy')
UNION ALL
SELECT To_date('04012014', 'mmddyyyy') + LEVEL - 1 ,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-
FROM all_tables
WHERE to_date('01-apr-2014','DD-
) t
where a.current_month_first_day(
Is there a way to get all the data in one line instead of showing nulls where there is data?
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
/
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'
-- 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(
order by t.h_date
/
ASKER
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
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.
Don't you need the empty days after 30-apr-2014 ?
ASKER
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
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
ASKER
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.
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/m m/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
/
(both with start on sunday and no days after the month)
with s1
as
(select to_date('01-04-2014','dd/m
,
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(
and t.h_date < add_months(startdate,1) -- end with the last day of the month
order by t.h_date
/
ASKER
How can I stop from the week starting with saturday? Everything looks good
"How can I stop from the week starting with saturday?
I don't understand. What does not look good ?
I don't understand. What does not look good ?
ASKER
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
data.JPG
Your week data setting is probably different :
with s1
as
(select to_date('01-04-2014','dd-m m-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 ?
with s1
as
(select to_date('01-04-2014','dd-m
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 ?
ASKER
Yes I get a 3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this worked. starts with a sunday
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.
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.
ASKER
Thanks though
ASKER
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
Thanks,
table-data.txt
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot. That helped
ASKER
from tab_data a,
(SELECT to_date('01-apr-2014','DD-
FROM all_tables
WHERE to_date('01-apr-2014','DD-
) t
where a.current_month_first_day(