Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
QueenKirsty
Starting Member
18 Posts |
 Posted - 2009-08-18 : 07:10:40
|
I have a history table with a series of events equipment items, including their location (ModuleID) and the date of the change event. A new record is added (from the Delete table) each time a recod is changed. For example...
ChangeID ChangeDate EquipmentID ModuleID EquipStatus 1 12/9/08 230 1789 Normal 2 13/9/08 450 1245 Normal 3 17/9/08 230 1789 Open 4 21/9/08 230 1899 Open 5 21/9/08 450 1674 Normal 6 22/9/08 450 2364 Normal
What I need to find is:Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this?Any and all advice appreciated!  |
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-08-18 : 08:12:47
|
got the answer:DECLARE @point_in_time datetime SET @point_in_time = '20080917'SELECT *FROM your_table INNER JOIN ( SELECT equipment_id , Max(change_date) As change_date FROM your_table WHERE change_date <= @point_in_time GROUP BY equipment_id ) As applicable_records ON applicable_records.equipment_id = your_table.equipment_id AND applicable_records.change_date = your_table.change_date |
 |
|
|
|
|
|
Finding record nearest to a particular date