select data from 2 rows into 1

Hi

I would like to extract records from a database table to a text file.
There are a lot of redundent data in the table so i was wondering whether i can do a some thing like below, to only select out some of the necessary records from two rows and combine then to one row.

Example
table emp_info
|Name|Emp_id|Work_time|
|ABCD|--01--|---0700---|
|BCDE|--02--|---0705---|
|BCDE|--02--|---1800---|
|ABCD|--01--|---1810---|
.
.
.

From the table above can i just extract to text file like this??
ABCD 01 0700 1810
BDCE 02 0705 1800
.
.
.


select distinct a.emp_id, a.name, min(a.work_time) in_time, b.out_time
from emp_info a, (select emp_id, max(work_time) out_time from emp_info group by emp_id) b
where a.emp_id = b.emp_id


Can some look through my script and see whether it can work out or not?? If not suggest ways to improve or correct it?? (i think something is wrong in the time part, dont think it can find the min/max date cos there are no comparision within 1 row, anyone can correct it??)

Thanks.

If your want to get only minimum and maximum time frame of any employee, you could get this result set without using subquery, as given below:

SELECT name,emp_id, min(work_time) wortime_in,max(work_time) worktime_out
FROM emp_info
GROUP BY name,emp_id
/

By the way to enter work_time into table what data type you are using, is it varchar2 or something else?????

Regards,

Thank you for the suggest, will try it out later and see whether it will do wat i want. The work_time is not a VARCHAR2...it's a DATE type

so is it ok that i do something like this??

SELECT name, emp_id, min(to_char(work_time, HH24:MI)) in_time, max(to_char(work_time, HH24:MI)) out_time,
FROM emp_info
GROUP BY name, emp_id
/

Thanks again.

I think i need to make some changes the the data that i wan to extract, so i was wondering whether the sql script above can be change to do the following...

The changes that i wan to make are:
Example
|Name|Emp_id|--Work_time--|
|ABCD|--01--|01/01/04 07:00|
|BCDE|--02--|01/01/04 07:05|
|BCDE|--02--|01/01/04 18:00|
|ABCD|--01--|01/01/04 18:10|
|ABCD|--01--|02/01/04 07:00|
|BCDE|--02--|02/01/04 07:10|
|BCDE|--02--|02/01/04 19:00|
.
.

extract to text file
ABCD 01 01/01/04 07:00 01/01/04 18:10
BDCE 02 01/01/04 07:05 01/01/04 18:00
ABCD 01 02/01/04 07:00
BCDE 02 02/01/04 07:10 02/01/04 19:00
.
.

The previous script does to work as it seem to select something like this, the overall min and max not the min and max on the same date.
ABCD 01 01/01/04 07:00 02/01/04 07:00
BDCE 02 01/01/04 07:05 02/01/04 19:00

I would like it to extract the min and max for each different employee on the same date(use the time for min and max) and if a employee does not have 2 work_time date that is the same (like ABCD on 02/01/04) it will have a null value for the out_time.

What modification should i make the the previous script

SELECT name, emp_id,
min(to_char(work_time, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(work_time, 'DD/MM/YY HH24:MI')) out_time,
FROM emp_info
GROUP BY name, emp_id
/

Thanks

Try this:

SELECT name, emp_id,
min(to_char(work_time, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(work_time, 'DD/MM/YY HH24:MI')) out_time,
FROM emp_info
GROUP BY name, emp_id, trunc(work_time)

If you want to provide a date every time when you run this query, Your query just fine, otherwise you need to add the date in your query to grouping data in date wise fashion that is given in second query below:

Query 1:
--------
SELECT name, emp_id,
min(to_char(work_time, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(work_time, 'DD/MM/YY HH24:MI')) out_time,
FROM emp_info
WHERE to_char(work_time,'dd-Mon-yyyy') = '01-Jun-2004'
GROUP BY name, emp_id

Query 2:
--------
SELECT name, emp_id, to_char(work_time,'dd-Mon-yyyy')
min(to_char(work_time, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(work_time, 'DD/MM/YY HH24:MI')) out_time,
FROM emp_info
GROUP BY name, emp_id, to_char(work_time,'dd-Mon-yyyy')
/

Thank you all for ur help.

The script works...but there are some small parts that i was wonder whether it can be fix

1. Like cases such as employee ABCD on 02/01/04 where he only have one entry for the work_time that day. Can i have a script that will put a null/blank value for the out_time.
The current script extracted something like this
ABCD 01 02-Jan-2004 07:00 07:00
I would like it to extract something like this
ABCD 01 02-Jan-2004 07:00

2. Can i have the extracted records to be display by the order of the emp_id and the date, cos the records are not display according to the order of their date. It kinda display everything with the order of the first dd value and ignore the rest Mon and yyyy value
The current script display something like this
ABCD 01 01-Jan-2004 07:00 18:10
ABCD 01 01-Mar-2004 07:00 18:25
ABCD 01 01-Feb-2004 07:00 18:15
ABCD 01 01-Dec-2003 07:00 17:55
ABCD 01 02-Jan-2004 07:00 07:00
.
.

I would like it to extract something like this
ABCD 01 01-Dec-2003 07:00 17:55
.
.
ABCD 01 01-Jan-2004 07:00 18:10
ABCD 01 02-Jan-2004 07:00
ABCD 01 03-Jan-2004 .........
.
ABCD 01 01-Feb-2004 07:00 18:15
.
.
ABCD 01 01-Mar-2004 07:00 18:25
.
.


The script i m using is
SELECT name, emp_id, to_char(work_time,'dd-Mon-yyyy')
min(to_char(work_time, 'HH24:MI')) in_time,
max(to_char(work_time, 'HH24:MI')) out_time,
FROM emp_info
GROUP BY name, emp_id, to_char(work_time,'dd-Mon-yyyy')
ORDER BY emp_id, to_char(work_time,'dd-Mon-yyyy')

Thanks.

Okay...i think i have solve the first case but the order of the dates is still a mess. Any problem in this script as the order by doesn't seem to work.

SELECT name, emp_id, to_char(work_time,'dd-Mon-yyyy')
min(to_char(work_time, 'HH24:MI')) in_time,
decode(max(to_char(work_time, 'HH24:MI')), min(to_char(work_time, 'HH24:MI')), null, max(to_char(work_time, 'HH24:MI'))) out_time
FROM emp_info
GROUP BY name, emp_id, to_char(work_time,'dd-Mon-yyyy')
ORDER BY emp_id, to_char(work_time,'dd-Mon-yyyy')

Thanks

Because you are using to_char function to change the format of date, to_char actually change the data type into character format so the order of your query is perfectly right (ascending order of alphabets), if you want to generate the output in date wise fashion you need to do miner modification in your query, as given below:

SELECT name, emp_id, to_date(work_time,'dd-Mon-yyyy')
min(to_char(work_time, 'HH24:MI')) in_time,
decode(max(to_char(work_time, 'HH24:MI')), min(to_char(work_time, 'HH24:MI')), null, max(to_char(work_time, 'HH24:MI'))) out_time
FROM emp_info
GROUP BY name, emp_id, to_char(work_time,'dd-Mon-yyyy')
ORDER BY emp_id, to_date(work_time,'dd-Mon-yyyy')


Note:
You can also use the column position number or column alias name in ORDER BY clause instead of using complete syntax. loot at an example:

SELECT name, emp_id, to_date(work_time,'dd-Mon-yyyy')
min(to_char(work_time, 'HH24:MI')) in_time,
decode(max(to_char(work_time, 'HH24:MI')), min(to_char(work_time, 'HH24:MI')), null, max(to_char(work_time, 'HH24:MI'))) out_time
FROM emp_info
GROUP BY name, emp_id, to_char(work_time,'dd-Mon-yyyy')
ORDER BY 2, 3

make the same changes in GROUP BY clause as well

GROUP BY name, emp_id, to_date(work_time,'dd-Mon-yyyy')

Initially when i try out the script it will give me this error msg
ERROR:
ORA-01841: (full) year must be between -4713
and +9999, and not be 0

So i made some changes to the date
to_date(work_time,'dd-Mon-yy')
And after that minor changes it work out fine.

Thanks for all ur help, i've learn a lot on GROUP BY and ORDER BY function through solving this problem.