I would like to find the last date/time for each unique Identification # before the LOCATION changes. For example...
I have Identification # 100 that repeats 10 times on 1/1/15 at Location 15. Later in the day on 1/1/15, Identification # 100 switches locations to Location 25 where it remains until 2/1/16. On 2/1/16, Identification # 100 switches from location 25 to location 10.
I would like to only see the last date/time from location 10 on 1/1/15, the last date/time from location 25 on 2/1/16, and so on. Below is my SQL code.
SELECT Step2_Table.Identification, [MasterData].RecordDateAndTime, [MasterData].LOCATION
FROM Step2_Table INNER JOIN [MasterData] ON Step2_Table.Identification = [MasterData].Identification
WHERE ((([MasterData].DetectionDate) Between [MinOfLastDate] And [MaxOfFirstDate]));
Here is an example of my output....
For this example, I would like the output to be
Identification Date/Time Location 100 11/22/13 14:28 10 100 11/22/13 14:29 10 100 11/22/13 14:30 10 100 11/22/13 14:31 10 100 11/22/13 14:32 10 100 11/22/13 14:43 9 100 11/22/13 14:52 9 100 11/22/13 14:52 10 100 11/22/13 16:10 9 100 11/22/13 16:15 9 100 11/22/13 16:19 9 100 11/22/13 16:20 9 100 11/22/13 16:27 9 100 11/22/13 16:29 9 100 11/22/13 16:31 9 100 11/22/13 16:59 9 100 11/22/13 17:27 12 100 11/22/13 17:55 12
Identification Date/Time Location 100 11/22/13 14:32 10 100 11/22/13 14:52 9 100 11/22/13 14:52 10 100 11/22/13 16:59 9 100 11/22/13 18:23 12
I appreciate the help