| Solve this classic query [message #206258] |
Wed, 29 November 2006 05:55  |
akshaye_2win
Messages: 2
Registered: November 2006
|
Junior Member |
|
|
Hello
I have a table with the follwing fields
Emp_code varchar2(6),
Att_date date,
shift_code varchar2(1)
Consider that a employees comes to office in different shift during a month
the table look like this
Emp_code DATE SHIFT
------------ ---------- --------
137670 12-NOV-2006 1
137670 13-NOV-2006 1
137670 14 -NOV-2006 2
137670 15-NOV-2006 3
137670 16-NOV-2006 4
137670 17-NOV-2006 1
137670 18-NOV-2006 1
I WANT OUTPUT IN FOLLOWING FORMAT
EMP_CODE SHIFT FROm TO
-------- ------ ---- --
137670 1 12-NOV-06 13-NOV-06
17-NOV-06 18-NOV-06
137670 2 14-NOV-06 14-NOV-06
137670 3 15-NOV-06 15-NOV-06
and so on........
Waiting for earliest reply
regards -akshay
[Updated on: Wed, 29 November 2006 05:57] Report message to a moderator |
|
|
|
|
|
| Re: Solve this classic query [message #206269 is a reply to message #206258] |
Wed, 29 November 2006 06:30  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Not exactly a classic problem...
| Quote: | create table temp_emp (emp_code number, shift_Date date, shift number);
insert into temp_emp values(137670, '12-NOV-2006', 1);
insert into temp_emp values(137670, '13-NOV-2006', 1);
insert into temp_emp values(137670, '14-NOV-2006', 2);
insert into temp_emp values(137670, '15-NOV-2006', 3);
insert into temp_emp values(137670, '16-NOV-2006', 4);
insert into temp_emp values(137670, '17-NOV-2006', 1);
insert into temp_emp values(137670, '18-NOV-2006', 1);
select emp_code
,shift
,start_date
,end_date
from (select emp_code
,shift
,shift_date start_date
,nvl(lead(shift_date) over (partition by emp_code,shift order by shift_Date),shift_date) end_date
,row_number() over (partition by emp_code,shift order by shift_Date) rnum
from temp_emp)
where mod(rnum,2)=1;
|
You really shouldn't call columns 'DATE'.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
