I hope someone can help me with this. I have been racking my poor stupid brain for days over this. I need a way to be able to create a view of the data in a single table. Let's call it tblStatus. Here is a list of fields for the example:
StatusId (Primary Key for this table)
StatusTypeId (Foreign Key to a static list of status types)
EquipmentId (Foreign key linking to tblEquipment)
EndDate (Date that this record finishes/finished being in effect)
Now the purpose of tblStatus is to store records about the location and usage of a piece of equipment. The StatusTypeId field links into the type of usage the equipement is used for in the particular record. There is more data in the table but it does not affect the current example.
The resulting data I need is:
List of all StatusId and EquipmentId for only the LATEST (EndDate) for each EquipmentId.
StatusID EquipmentID EndDate StatusTypeID 8427 14402 30-Sep-08 1 8148 14402 14-May-07 3 7669 14402 07-Jul-01 15 8272 14403 27-Feb-08 2 8287 14403 11-Sep-06 1 7998 14403 20-May-05 6 7670 14403 25-Dec-02 15
In the above example data only records 8427 and 8272 would be what I wanted returned.
Can anyone point me in the right direction please?