I need to build a unit history table to track daily changes in an unit operations table. I want to compare a copy of the operations table to a result set containing the entries with the latest date for each unit from the history table, delete duplicates from the copy table and insert the remainder into the history table.

My problem is the query returning the entries with the latest date per unit. I know how to return all units with the max date in the table but this misses any units that didn't change on the higest date in the table. This is resulting in duplicate entries every other day.

This is the query so far. The problem is with the AS b part of the delete statement.

>begin tran
>
>insert into mobile_changes
>(Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES)
>
>select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
>from A1MobList union all
>select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
>from A2MobList union all
>select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
>from R1MobList union all
>select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
>from R2MobList
>GO
>
>delete mobile_changes from mobile_changes as a,
>(select * from mobile_history where date_time = (select max(date_time)
>from mobile_history)) as b
>where
>a.mobile = b.mobile and
>a.fid = b.fid and
>a.rid = b.rid and
>a.pin = b.pin and
>a.commission = b.commission and
>a.barred = b.barred and
>a.barred_ACSE = b.barred_ACSE and
>a.LES = b.LES
>GO
>
>insert into mobile_history
>(Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES)
>select
>Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
>from Mobile_Changes
>GO
>
>delete from mobile_changes
>GO
>
>commit tran

You need to get the max(date_time) per unit, and then use that to find the latest record for the unit:
Note that if (unit, date_time) are not unique in the table then you could get back more than 1 row per unit.

not all databases support that structure, tony

a correlated subquery is a good alternative, works even in Access:

Thanks