Home » SQL & PL/SQL » SQL & PL/SQL » SQL question
SQL question [message #192472] Tue, 12 September 2006 09:17 Go to next message
jeannezhang
Messages: 21
Registered: May 2005
Junior Member
Hi, SQL Experts,

I would like to ask your help.

I have a table like:

student_id p_date p_code
stud_1 09/01/2000 E
stud_1 10/20/2000 W
stud_1 12/01/2000 R
stud_1 12/20/2000 E
stud_1 01/12/2001 W
stud_1 02/20/2002 R
stud_1 09/03/2002 E
stud_2 09/02/2000 E
stud_2 02/20/2001 W
stud_2 09/02/2002 E
stud_2 09/03/2003 E
stud_3 09/02/2000 E
stud_3 09/03/2001 E

E—Enroll; R—Re-enter; W—Withdrawn

I would like to have a SQL to list the students_id and p_date:
1.The first p_date with student_id if the student never withdrawn (no “W” p_code)
2.The immediate p_date (either “E” or “R”) right after the last “W” date for the student.

The data should be like:

Student_id p_date
Stud_1 02/20/2002
Stud_2 09/02/2002
Stud_3 09/02/2000

Thanks a lot for any help!
Re: SQL question [message #192500 is a reply to message #192472] Tue, 12 September 2006 11:41 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Here u go...
Not sure about the performance..

CREATE TABLE tab_test
(
student_id  VARCHAR2(20),
p_date      DATE,
p_code      VARCHAR2(1)
);
insert into  tab_test values('stud_1',to_date('09/01/2000','MM/DD/YYYY'),'E');
insert into  tab_test values('stud_1',to_date('10/20/2000','MM/DD/YYYY'),'W');
insert into  tab_test values('stud_1',to_date('12/01/2000','MM/DD/YYYY'),'R');
insert into  tab_test values('stud_1',to_date('12/20/2000','MM/DD/YYYY'),'E');
insert into  tab_test values('stud_1',to_date('01/12/2001','MM/DD/YYYY'),'W');
insert into  tab_test values('stud_1',to_date('02/20/2002','MM/DD/YYYY'),'R');
insert into  tab_test values('stud_1',to_date('09/03/2002','MM/DD/YYYY'),'E');

insert into  tab_test values('stud_2',to_date('09/02/2000','MM/DD/YYYY'),'E');
insert into  tab_test values('stud_2',to_date('02/20/2001','MM/DD/YYYY'),'W');
insert into  tab_test values('stud_2',to_date('09/02/2002','MM/DD/YYYY'),'E');
insert into  tab_test values('stud_2',to_date('09/03/2003','MM/DD/YYYY'),'E');

insert into  tab_test values('stud_3',to_date('09/02/2000','MM/DD/YYYY'),'E');
insert into  tab_test values('stud_3',to_date('09/03/2001','MM/DD/YYYY'),'E');
 



select query:


(SELECT   student_id, MAX (p_date_1) p_date
     FROM (SELECT student_id, p_date, p_code,
                  LEAD (p_date) OVER (PARTITION BY student_id ORDER BY student_id,
                   p_date) p_date_1
             FROM tab_test
            WHERE student_id IN (SELECT student_id
                                   FROM tab_test
                                  WHERE p_code = 'W'))
    WHERE p_code = 'W'
 GROUP BY student_id)
UNION
(SELECT   student_id, MIN (p_date)
     FROM tab_test
    WHERE student_id NOT IN (SELECT student_id
                               FROM tab_test
                              WHERE p_code = 'W')
 GROUP BY student_id)
 


Naveen
Re: SQL question [message #192508 is a reply to message #192500] Tue, 12 September 2006 12:25 Go to previous messageGo to next message
jeannezhang
Messages: 21
Registered: May 2005
Junior Member
Thank you very much. It works.
Re: SQL question [message #192548 is a reply to message #192472] Tue, 12 September 2006 18:08 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

SQL> select * from foo;

STUDENT_ID P_DATE P
-------------------- --------- -
stud_1 01-SEP-00 E
stud_1 20-OCT-00 W
stud_1 01-DEC-00 R
stud_1 20-DEC-00 E
stud_1 12-JAN-01 W
stud_1 20-FEB-02 R
stud_1 03-SEP-02 E
stud_2 02-SEP-00 E
stud_2 20-FEB-01 W
stud_2 02-SEP-02 E
stud_2 03-SEP-03 E
stud_3 02-SEP-00 E
stud_3 03-SEP-01 E

13 rows selected.
============

Now the SQL .

select student_id ,min(p_date) from (
select student_id,case when p_code='W' then 'W' else 'Z' end zcode,p_date,
max(case when p_code='W' then p_date
else to_date('01/01/1000','dd/mm/yyyy') end ) over
(partition by student_id
order by student_id,case when p_code='W' then 'W' else 'Z' end ,p_date) lvpdt
from foo
) where p_date > lvpdt and zcode='Z'
group by student_id
/

STUDENT_ID MIN(P_DAT
-------------------- ---------
stud_1 20-FEB-02
stud_2 02-SEP-02
stud_3 02-SEP-00



--> 01/01/1000 is an arbitary earlier date chosen.

---> the case p_code helps to treat the rows in two logical groups. W and those that are not W.

Srini

Re: SQL question [message #194993 is a reply to message #192472] Tue, 26 September 2006 08:04 Go to previous message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Hi,
This may help you i am getting the result perfectly
select student_id, p_date from

(select student_id, p_date, rownum rnum from tab_test)
where rnum in (select min(rnum)
from (Select student_id, rownum rnum, p_code from tab_test) where student_id not in (select distinct student_id from tab_test where p_code = 'W')
group by student_id)
union
select student_id, p_date from
(select student_id, p_date, rownum rnum from tab_test)
where rnum in (select max(rnum)+1
from (Select student_id, rownum rnum, p_code from tab_test) where student_id in (select distinct student_id from tab_test where p_code = 'W')
and p_code = 'W' group by student_id);

The output that i am getting is:

STUDENT_ID P_DATE
-------------------- ---------
stud_1 20-FEB-02
stud_2 02-SEP-02
stud_3 02-SEP-00

Regards
Jaydeep


Previous Topic: NEED TO SHOW THE TOP 5 DATA
Next Topic: Problem with Group by rollup query
Goto Forum:
  


Current Time: Sat Dec 03 22:13:10 CST 2016

Total time taken to generate the page: 0.06956 seconds