Home » SQL & PL/SQL » SQL & PL/SQL » Solve this classic query
Solve this classic query [message #206258] Wed, 29 November 2006 05:55 Go to next message
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 #206266 is a reply to message #206258] Wed, 29 November 2006 06:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you call this a 'classic query' in your title? Maybe because it gets answered nearly every other day?
Search the forum for pivot query.
Re: Solve this classic query [message #206269 is a reply to message #206258] Wed, 29 November 2006 06:30 Go to previous message
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'.
Previous Topic: update flag with first occurence of a column
Next Topic: Passing CLOB to procedure in sqlplus
Goto Forum:
  


Current Time: Tue Dec 06 12:04:40 CST 2016

Total time taken to generate the page: 0.12762 seconds