OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Problem [message #361889]

Fri, 28 November 2008 07:40

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

Dear Group Members
I am working on attendance form. Attendance of employees of every day is stored in this form.
I want to get the max time_in of an employee with time out is null. I have made this query.
But the result is not as i require.

select time_in from attendance 
where time_in = (select max(time_in) from attendance 
where emp_code = '001820')
and time_out is null;

suppose data is as under

emp_code      time_in                     time_out
001820        25-nov-08 09:02 AM         25-nov-08 05:15 PM
001820        26-nov-08 09:04 AM
001820        27-nov-08 09:02 AM         25-nov-08 05:15 PM
001820        28-nov-08 09:05 AM

I have to make the query to get the last row.

Thanks & regards

[Updated on: Fri, 28 November 2008 07:55]

Report message to a moderator

Re: Query Problem [message #361894 is a reply to message #361889]

Fri, 28 November 2008 08:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You want the last because it is the max time or because time_out is null? In other words, if time_out is not null do you stil want this row or another one?

Also always post your Oracle version (4 decimals).

And a representative test case: create table and insert statements along with the result you want with these data will help.

Regards
Michel

Report message to a moderator

Re: Query Problem [message #361898 is a reply to message #361889]

Fri, 28 November 2008 08:06

_jum
Messages: 577
Registered: February 2008

Senior Member

The query should give the last row as result (as wanted).

WITH a AS
     (
        SELECT '001820' emp_code,
               TO_DATE('25-nov-08 09:02 AM', 'DD-MON-YY HH:MI AM') time_in,
               TO_DATE('25-nov-08 05:15 PM', 'DD-MON-YY HH:MI AM') time_out
          FROM DUAL
        UNION ALL
        SELECT '001820', TO_DATE('26-nov-08 09:04 AM', 'DD-MON-YY HH:MI AM'),
               NULL
          FROM DUAL
        UNION ALL
        SELECT '001820', TO_DATE('27-nov-08 09:02 AM', 'DD-MON-YY HH:MI AM'),
               TO_DATE('25-nov-08 05:15 PM', 'DD-MON-YY HH:MI AM')
          FROM DUAL
        UNION ALL
        SELECT '001820', TO_DATE('28-nov-08 09:05 AM', 'DD-MON-YY HH:MI AM'),
               NULL
          FROM DUAL)
SELECT time_in
  FROM a
 WHERE time_in = (SELECT MAX(time_in)
                    FROM a
                   WHERE emp_code = '001820') AND time_out IS NULL;

time_in
28.11.2008 09:05:00

Report message to a moderator

Re: Query Problem [message #361903 is a reply to message #361898]

Fri, 28 November 2008 09:07

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

Thanks for reply
Dear Jum
as you made the query has the quite well results. But i have also used the same techniqe but the results are not as i require.
the sample data is as under

              Attendance
EMP_CODE TIME_IN	        TIME_OUT
-------- -------                --------    
001820	11/18/2008 8:59:58 AM	11/18/2008 5:13:43 PM
001820	11/19/2008 9:00:08 AM	11/19/2008 5:23:03 PM
001820	11/20/2008 9:01:02 AM	11/20/2008 5:10:06 PM
001820	11/21/2008 9:18:43 AM	11/21/2008 6:35:47 PM
001820	11/22/2008 9:13:08 AM	11/22/2008 4:35:38 PM
001820	11/24/2008 5:14:33 PM	11/27/2008 9:04:43 AM
001820	11/27/2008 9:05:07 AM	11/27/2008 5:18:59 PM
001820	11/28/2008 9:07:00 AM	11/28/2008 5:43:31 PM

and i have used this query.

SELECT time_in
FROM attendance
WHERE time_in = (SELECT MAX(time_in)
                FROM attendance
                WHERE emp_code = '001820') AND time_out IS NULL;

Result
Time_in
11/28/2008 9:07:00 AM
11/28/2008 9:07:00 AM
please tell me why it produce results like this.

I require the row which have max time_in = 11/28/2008 9:07:00 AM
and it has time_out = 11/28/2008 5:43:31 PM so it have to return null if time_in = 11/28/2008 9:07:00 AM and it has time_out = NULL then it return time_in 11/28/2008 9:07:00 AM.

Thanks and regards

Report message to a moderator

Re: Query Problem [message #361904 is a reply to message #361903]

Fri, 28 November 2008 09:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 28 November 2008 15:03

You want the last because it is the max time or because time_out is null? In other words, if time_out is not null do you stil want this row or another one?

Also always post your Oracle version (4 decimals).

And post a representative test case: create table and insert statements along with the result you want with these data will help.

Regards
Michel

Report message to a moderator

Re: Query Problem [message #361908 is a reply to message #361903]

Fri, 28 November 2008 09:12

_jum
Messages: 577
Registered: February 2008

Senior Member

@sweetkhaliq
Wich type are Your columns TIME_IN and TIME_OUT.
To compare them - date columns must have the type DATE.

Please follow the forum rules as said by @Michel.

[Updated on: Fri, 28 November 2008 09:14]

Report message to a moderator

Re: Query Problem [message #361909 is a reply to message #361908]

Fri, 28 November 2008 09:17

sweetkhaliq
Messages: 200
Registered: April 2006

Senior Member

Yes i have date datatype for both time_in and time_out.

Quote:

You want the last because it is the max time or because time_out is null? In other words, if time_out is not null do you stil want this row or another one?

Yes i want the last because it is the max time. In other words, if time_out is not null i still want this row.

thanks and regards

Report message to a moderator

Re: Query Problem [message #361913 is a reply to message #361909]

Fri, 28 November 2008 09:37

_jum
Messages: 577
Registered: February 2008

Senior Member

@sweetkhaliq
I'd bet the posted statement should give the correct result with the posted data.

But You didn't post Your Oracle version, nor a test case, so we only can guess.

Report message to a moderator

Re: Query Problem [message #361921 is a reply to message #361913]

Fri, 28 November 2008 10:26

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You want the last row or just the last date?

Still: post a representative test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Report message to a moderator

Previous Topic:	SYS_CONNECT_BY_PATH
Next Topic:	2 employes in the register table, it is possible?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 15 07:59:42 CST 2025