Home » SQL & PL/SQL » SQL & PL/SQL » Find the Invalid Entry in the table (Oracle 10g, Win XP)
Find the Invalid Entry in the table [message #411749] Mon, 06 July 2009 09:56 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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,

 PID L
--------- - 
        2 b 



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 #411753 is a reply to message #411749] Mon, 06 July 2009 10:09 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have a look at lead and/or lag, and then subtract the date value in the actual row from the one in the previous/following row.
Re: Find the Invalid Entry in the table [message #411754 is a reply to message #411749] Mon, 06 July 2009 10:09 Go to previous messageGo to next message
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
Re: Find the Invalid Entry in the table [message #411820 is a reply to message #411754] Tue, 07 July 2009 00:50 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT pid, LOCATION, date1,
       LAG (date1, 1) OVER (PARTITION BY pid ORDER BY date1) AS next_val,
       date1-LAG(date1, 1) OVER (PARTITION BY pid ORDER BY date1)
  FROM test_score

[Updated on: Tue, 07 July 2009 00:58] by Moderator

Report message to a moderator

Previous Topic: Better way of writing SQL ( with same output ofcourse)
Next Topic: unique constraint violation for two columns.. very confusing!
Goto Forum:
  


Current Time: Tue Feb 11 13:37:33 CST 2025