Find the Invalid Entry in the table [message #411749] |
Mon, 06 July 2009 09:56  |
|
Hi,
I have a following table,
I have a table called Test_score which stores the test results by student. A Student is not allowed to take a test for 30 days after failing in the first time.
create table test_score(pid number(2),location char(1),date1 date);
insert into test_score values(1,'a','1-Feb-2009')
insert into test_Score values(2,'b','1-june-2009');
insert into test_Score values(3,'c','1-oct-2008');
insert into test_Score values(1,'a','1-june-2009');
insert into test_Score values(2,'b','1-june-2009');
insert into test_Score values(5,'e','1-june-2009');
insert into test_Score values(6,'f','1-june-2009');
Please do use the same Date Format (dd-mon-yyyy)
SQL> select * from test_score;
PID L DATE1
---------- - ---------
1 a 01-FEB-09
2 b 01-JUN-09
3 c 01-OCT-08
1 a 01-JUN-09
2 b 01-JUN-09
5 e 01-JUN-09
6 f 01-JUN-09
PID COUNT(PID)
---------- ----------
1 2
2 2
3 1
5 1
6 1
SQL> select * from test_score where pid
in ( select pid from test_score group by pid having count(pid)>=2) order by pid
2 /
PID L DATE1
--------- - ---------
1 a 01-JUN-09
1 a 01-FEB-09
2 b 01-JUN-09
2 b 01-JUN-09
Now 2 Students 1,2 have taken the tests twice But The time duration for Pid=1 is more than 30 days, So He is valid,But the time duration for Pid=2 is less than 30 days so he cannot take tests. Its invalid,
So I need the following output,
Could anybody help here.
[Updated on: Tue, 07 July 2009 00:58] by Moderator Report message to a moderator
|
|
|
|
Re: Find the Invalid Entry in the table [message #411754 is a reply to message #411749] |
Mon, 06 July 2009 10:09   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | So I need the following output,
|
I don't know a client that can display not aligned result.
SQL> create table test_score(pid number(2),location char(1),date1 date);
Table created.
SQL>
SQL> insert into test_score values(1,'a','1-Feb-2009')
2 /
insert into test_score values(1,'a','1-Feb-2009')
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
I failed to execute your test case.
Why? Because strings are not dates.
Have a look at LAG/LEAD functions.
Regards
Michel
|
|
|
|