I'm trying to create a history table to collect all fields from table A based on if the test_date changes for each user. So basically, for member 001, if A.test_date is newer than the max test date for member 001 in history table, then insert. Can this be done with an update join? I've got a cursor that goes through each member and sets max(test_date) then compares with a.test_date, but it is taking way too long for some reason. If there are multiple entries for each member in the history table, how do I join on the one with the max(test_date)?
Show us some sample data to help explain the problem. I'm not following your explanation.
As I understand I have given the below query. Please try this. If any please provide sample
select col1, col2....
from table1 t
left outer join
(
select max(test_date) mdate, user
from table1
group by user
) m
on t.user = m.user
and t.test_date >m.mdate
That works, thank you!