Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem (oracle 9i)
Query Problem [message #361889] Fri, 28 November 2008 07:40 Go to next message
sweetkhaliq
Messages: 189
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Query Problem [message #361898 is a reply to message #361889] Fri, 28 November 2008 08:06 Go to previous messageGo to next message
_jum
Messages: 509
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
Re: Query Problem [message #361903 is a reply to message #361898] Fri, 28 November 2008 09:07 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
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
Re: Query Problem [message #361904 is a reply to message #361903] Fri, 28 November 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: Query Problem [message #361908 is a reply to message #361903] Fri, 28 November 2008 09:12 Go to previous messageGo to next message
_jum
Messages: 509
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 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
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
Re: Query Problem [message #361913 is a reply to message #361909] Fri, 28 November 2008 09:37 Go to previous messageGo to next message
_jum
Messages: 509
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.

Re: Query Problem [message #361921 is a reply to message #361913] Fri, 28 November 2008 10:26 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: SYS_CONNECT_BY_PATH
Next Topic: 2 employes in the register table, it is possible?
Goto Forum:
  


Current Time: Sat Dec 10 01:15:17 CST 2016

Total time taken to generate the page: 0.12881 seconds