Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate/Split rows per week
Duplicate/Split rows per week [message #243606] Fri, 08 June 2007 00:17 Go to next message
Messages: 64
Registered: May 2005
Hi Experts,

I have Vista Home edition my laptop and I have installed oracle recently. While installing oracle gave me a link
with my laptop name to use oracle from browser. (http://ktishna-laptop.....).
This is first time am looking at 10g. I have created an user from the web but am not able to create roles and provide privileges to my user. I hope we can do it using sql*plus by writing grant create/insert to user name.
But i would like to know how to do it from enterprise manager which looks like an web application page. I am trying to do it with foll navigation
(database->administration-> users->edit->) after this i don't find any values under roles, object privileges tabs to provide privileges to my user name.
Do we need to setup some thing before doing this? Can some one help me regarding?

And one more help needed on SQL question:

I have following table and data.

Date1 Date2 val
-------- --------- ---
5/1/2007 5/15/2007 42
5/16/2007 6/15/2007 43

I am trying to get the put as mentioned below. o/p should display the value per week. Week starts on Sat(7) and ends on Friday(6). All i need to do is break into display value per week.

required Output:
From To val
-------- --------- ---
5/1/2007 5/4/2007 42
5/5/2007 5/11/2007 42
5/12/2007 5/15/2007 42
5/16/2007 5/18/2007 43
5/19/2007 5/25/2007 43
5/26/2007 6/1/2007 43
6/2/2007 6/8/2007 43
6/9/2007 6/15/2007 43

select date1,
lag(next_day(date1,'friday')) over(order by date1) nxt_wk1,
from dates;

I have got the output:
From To Val
---- --------- ---
5/16/2007 5/4/2007 42

similar query:

select date1, date2, val,
lag(next_day(date1,'friday')) over(order by(next_day(date1,'friday'))) nx11,
lag(next_day(date2,'friday')) over(order by(next_day(date2,'friday'))) nx12,
lead(next_day(date1,'friday')) over(order by(next_day(date1,'friday'))) nx21,
lead(next_day(date2,'friday')) over(order by(next_day(date2,'friday'))) nx22
from dates;

I hope it is working fine for a single record. If yes, it has to apply for all the rows in a table using single sql statement. OR I would appreciate if some one could help me in their own way.I need lots of inputs from experts regarding this. So that i can understand better and i can learn more tech stuff from the forum.

Thanks in advance,

[Updated on: Fri, 08 June 2007 00:49] by Moderator

Report message to a moderator

Re: Help about 10g Administration [message #243610 is a reply to message #243606] Fri, 08 June 2007 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't post 2 questions in the same topic. All the more if they are related to different forums.

Repost you EM question in EM forum, we'll answer to your SQL question here. I modified your topic subject to fit your SQL question.

Please read and apply How to format your posts.


[Updated on: Fri, 08 June 2007 00:49]

Report message to a moderator

Re: Duplicate/Split rows per week [message #243633 is a reply to message #243606] Fri, 08 June 2007 02:28 Go to previous message
Michel Cadot
Messages: 65150
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop table t;
create table t (date1 date, date2 date, val integer);
insert into t values(to_date('5/1/2007','MM/DD/YYYY'),
insert into t values(to_date('5/16/2007','MM/DD/YYYY'),
alter session set nls_date_format='MM/DD/YYYY' nls_date_language=american;
SQL> select * from t;
DATE1      DATE2             VAL
---------- ---------- ----------
05/01/2007 05/15/2007         42
05/16/2007 06/15/2007         43

2 rows selected.

SQL> with 
  2    weeks as (
  3      select mindate+7*(rownum-1) begwk,
  4             mindate+7*(rownum-1)+6 endwk
  5      from (select next_day(min(date1)-6,'SATURDAY') mindate,
  6                   next_day(max(date2)+6,'FRIDAY') maxdate from t)
  7      connect by level <= trunc((maxdate-mindate)/7)
  8    )
  9  select greatest(nvl(date1, date '0001-01-01'),begwk) "From",
 10         least(nvl(date2, date '9999-12-31'),endwk) "To",
 11         val "Val"
 12  from t partition by (date1,val)
 13       right outer join weeks w
 14       on ( 1 = 1 )
 15   where w.begwk >= t.date1-6 and w.endwk <= t.date2+6
 16  /
From       To                Val
---------- ---------- ----------
05/01/2007 05/04/2007         42
05/05/2007 05/11/2007         42
05/12/2007 05/15/2007         42
05/16/2007 05/18/2007         43
05/19/2007 05/25/2007         43
05/26/2007 06/01/2007         43
06/02/2007 06/08/2007         43
06/09/2007 06/15/2007         43

8 rows selected.

Previous Topic: Case and Decode
Next Topic: Doubts abt various data objects...
Goto Forum:

Current Time: Mon Aug 21 14:55:15 CDT 2017

Total time taken to generate the page: 0.10868 seconds