Find new location value??

Hi..

I was wondering whether this can be done or not...

I alter my table by adding a new column to store employee's new location...now i need to populate the column with their new location...which i the part i do not know how to do.

I break this up so it is easier to understand
1) Need to first determine whether employee name are repeated or not in the table

2) If the employee's name occur only once then the new location column value should just be the old location value.

3) But when an employee's name occur more than one time on the table...then those few rows with the same employee name needs to be futher compare on their create date to know the new location value.

Example:
|Name|O_loc|-C_date-|
|ABCD|--A--|1/1/2003|
|BCDE|--A--|1/1/2003|
|ABCD|--B--|1/6/2003|
|ABCD|--C--|1/9/2003|

Result in
|Name|O_loc|-C_date-|N-loc|
|ABCD|--A--|1/1/2003|--C--|
|BCDE|--A--|1/1/2003|--A--|
|ABCD|--B--|1/6/2003|--C--|
|ABCD|--C--|1/9/2003|--C--|

*find the max(C_date) among the 3 rows with employee by the same name, ABCD, then put the O_loc value of that latest row into the 3 other N_loc column value.
this is the part i really dont know whether can be done or not

If this way can not be accomplish, can anyone suggest a better way to do it...the main thing that i really need is to find out the lastest location of an employee...

Thanks


regards,
YuLing

This might be tough enough query for you. However, try to understand the attached query yourself, if you need explaination, let me know.

I think i can understand most part of the query...there is just one small part i dont understand



Wont this means that N_loc dont get an O_loc value if the column that have the same name matches with both a and x but just that they dont have the same C_date as y??

Example:
table y
|Name|--C_date--|
|ABCD|1/9/2003|
|BCDE|-1/1/2003-|

|Name|O_loc|--C_date--|N-loc|
|ABCD|--A--|1/1/2003|--?--|
|BCDE|--A--|-1/1/2003-|--A--|
|ABCD|--B--|1/6/2003|--?--|
|ABCD|--C--|-1/9/2003-|--C--|


Anyhow i try out the query but i got this error message

Good, you understand correctly what i meant by? to prevent the duplication of record you must use 'distint' in your query, if you have more than one record with same old_loc and c_date as well. Look at the attached query now.

Thank you so much...after adding the distinct the query work like a charm.

Cant thank you enough...u really are a life saver...have been having many sleepless nite for the whole week trying to figure it out.

Thanks again!


YuLing

Hi shafique...

Sorry to have bother u again, i need to do the same concept thing as i did before for o_loc to find the n_loc

Example:
|Name|-Emp_id|-C_date-|
|ABCD|--001--|1/1/2003|
|BCDE|--002--|1/1/2003|
|ABCD|--123--|1/6/2003|
|ABCD|--234--|1/9/2003|

Result in
|Name|-Emp_id|-C_date-|New_id|
|ABCD|--001--|1/1/2003|--234--|
|BCDE|--002--|1/1/2003|--002--|
|ABCD|--123--|1/6/2003|--234--|
|ABCD|--234--|1/9/2003|--234--|

I change the previous query as below:


it work find before for old location to latest location but when i do the above query to find lastest emp_id it gives me an error msg even after i put the distinct



Could u please have a look at the query above to see where did i go wrong and perhaps help me to correct it??

Thanks.

Please replace line 2 with the following statment:

set a.new_id= (select max(x.emp_id)

because more than one emp_id can use the same name and date. In oreder to get the most recent emp_id use max() function as shown above.


Regards,