Tricky SQL query (any sql experts here?)

[petrjanda]

Ok here is the data you KNOW - Aircraft ID, and a Timestamp before you run the query.

You have a table that tracks every change done to a Part (instead of UPDATE, it does INSERT INTO on every update to hold a complete history of the PartID)

[TheLexx]

It looks like you just need to use a query for date ranges.

http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm

[GoldenMouse]

It's too late to come up with a query for you; I tried, but my brain is too fried to pull it together. I'll try to come back and answer this better, but it seems as if you need a subquery or two in there.

Start with a query that returns the most recent record before your stated time for a given part and a given aircraft:

SELECT *
FROM `TheTable`
WHERE `PartID` = ?
AND `AircraftID` = ?
AND `RecordCreatedAt` < ?
ORDER BY `RecordCreatedAt` DESC
LIMIT 1

You then need to wrap this such that it runs once for each PartID on that aircraft. I know it can be done, but it's past 2am right now.