Identify current records in a single table

[Ronbo]

Hi Folks,

I have a table containing information about climate sensors I manage. I need to determine the most recent record for all sensors by serial number according to the most recent calibration date for that specific sensor and then set that sensor's status to "Current".

I currently have a script that sorts the table by serial # and calibration date and then does a row-wise comparison to determine if two adjacent records are the same. From this result I then create a new variable called Current Status. The script seems overly cumbersome and time consuming and I'm thinking there is a more cleaver and less computational intensive way to figure this out. Thanks.

[Anuviel]

I currently have a script that sorts the table by serial # and calibration date and then does a row-wise comparison to determine if two adjacent records are the same.

Why same? Do you have duplicate records? Is your sensor information in one table and your calibration in another related by sensor id?

[Ronbo]

I have multiple entries for a given sensor by serial # based on the date it was installed or removed from a particular location. The table looks like this.

Serial # Date Removd/Instald Calib Date Status

Q488 5/15/05 6/1/05

Q488 6/15/07 7/21/07 Current

Q489 5/12/03 5/19/03

Q489 4/20/05 5/1/05

Q489 9/23/06 10/1/06 Current

R064 6/1/04

R064 7/2/05

R064 8/3/06 8/15/06 Current