Hi,

I have a table that has sample records like below:

Emp No. Unique ID From Date To Date
11 1234 01-Jan-2005 30-Sep-2005
11 2345 01-Oct-2005 31-Oct-2005
11 3456 01-Nov-2005 31-Oct-2005


The task that I have is to find out records where the From Date is
greater than the To Date. This is needed because the 3rd record in the
example above is actually an erroneous record which needs to be
deleted. The query that I was able to write was returning the 2nd
record also because 01-Oct-2005 is greater than 30-Sep-2005. Please
help me with the query which would return only the 3rd record.

Any help would be appreciated...thanks!
Sandy

On May 5, 3:00*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
You've written the query already, albeit in text rather than SQL; I
don't understand what query you've written to return the data you
report as it's likely far more complex than it needs to be.
'Translate', if you will, your English text into SQL and you'll have
your answer:

"find out records where the From Date is greater than the To Date."

The SQL, as shown below, should be easy to write from that description
--

SQL> create table sandy(
2 emp_on number,
3 unique_id number,
4 from_date date,
5 to_date date
6 );

Table created.

SQL>
SQL> insert all
2 into sandy
3 values(11,1234,to_date('01-Jan-2005','DD-Mon-YYYY'),to_date('30-
Sep-2005','DD-Mon-YYYY') )
4 into sandy
5 values(11,2345,to_date('01-Oct-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
6 into sandy
7 values(11,3456,to_date('01-Nov-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
2 from sandy
3 where to_date < from_date;

EMP_ON UNIQUE_ID FROM_DATE TO_DATE
---------- ---------- --------- ---------
11 3456 01-NOV-05 31-OCT-05

SQL>


David Fitzjarrell

On May 5, 3:00*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
You've written the query already, albeit in text rather than SQL; I
don't understand what query you've written to return the data you
report as it's likely far more complex than it needs to be.
'Translate', if you will, your English text into SQL and you'll have
your answer:

"find out records where the From Date is greater than the To Date."

The SQL, as shown below, should be easy to write from that description
--

SQL> create table sandy(
2 emp_on number,
3 unique_id number,
4 from_date date,
5 to_date date
6 );

Table created.

SQL>
SQL> insert all
2 into sandy
3 values(11,1234,to_date('01-Jan-2005','DD-Mon-YYYY'),to_date('30-
Sep-2005','DD-Mon-YYYY') )
4 into sandy
5 values(11,2345,to_date('01-Oct-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
6 into sandy
7 values(11,3456,to_date('01-Nov-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
2 from sandy
3 where to_date < from_date;

EMP_ON UNIQUE_ID FROM_DATE TO_DATE
---------- ---------- --------- ---------
11 3456 01-NOV-05 31-OCT-05

SQL>


David Fitzjarrell

On May 5, 3:00*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
You've written the query already, albeit in text rather than SQL; I
don't understand what query you've written to return the data you
report as it's likely far more complex than it needs to be.
'Translate', if you will, your English text into SQL and you'll have
your answer:

"find out records where the From Date is greater than the To Date."

The SQL, as shown below, should be easy to write from that description
--

SQL> create table sandy(
2 emp_on number,
3 unique_id number,
4 from_date date,
5 to_date date
6 );

Table created.

SQL>
SQL> insert all
2 into sandy
3 values(11,1234,to_date('01-Jan-2005','DD-Mon-YYYY'),to_date('30-
Sep-2005','DD-Mon-YYYY') )
4 into sandy
5 values(11,2345,to_date('01-Oct-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
6 into sandy
7 values(11,3456,to_date('01-Nov-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
2 from sandy
3 where to_date < from_date;

EMP_ON UNIQUE_ID FROM_DATE TO_DATE
---------- ---------- --------- ---------
11 3456 01-NOV-05 31-OCT-05

SQL>


David Fitzjarrell

On May 5, 3:00*am, Sandy80 <svarshneym... (AT) gmail (DOT) com> wrote:
You've written the query already, albeit in text rather than SQL; I
don't understand what query you've written to return the data you
report as it's likely far more complex than it needs to be.
'Translate', if you will, your English text into SQL and you'll have
your answer:

"find out records where the From Date is greater than the To Date."

The SQL, as shown below, should be easy to write from that description
--

SQL> create table sandy(
2 emp_on number,
3 unique_id number,
4 from_date date,
5 to_date date
6 );

Table created.

SQL>
SQL> insert all
2 into sandy
3 values(11,1234,to_date('01-Jan-2005','DD-Mon-YYYY'),to_date('30-
Sep-2005','DD-Mon-YYYY') )
4 into sandy
5 values(11,2345,to_date('01-Oct-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
6 into sandy
7 values(11,3456,to_date('01-Nov-2005','DD-Mon-YYYY'),to_date('31-
Oct-2005','DD-Mon-YYYY') )
8 select * from dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
2 from sandy
3 where to_date < from_date;

EMP_ON UNIQUE_ID FROM_DATE TO_DATE
---------- ---------- --------- ---------
11 3456 01-NOV-05 31-OCT-05

SQL>


David Fitzjarrell