I am going to try this fiddle thing. I did not do very much as far as inserting sample data because it gave me errors and erased my work. So Widget Fiddle
These two tables are barely related. One tracks inspections done on a widget and the other tracks where the widget is in the factory as of a specific date and timeamong other things.
What I need to know is where the widget was at the time a specific inspection occurred. A widget event could be anything but when it moves to a different part of the factory the locationID is not null. Otherwise it is null.
Inspections can occur at anytime in any location. So they are really only related by the fact that the widgetIDs match.
What I am looking for is something like. For all inspections show me the location the widget was in when it was performed.
I have seen something like select widgetID, InspectionDatTime, InspectionResult, LocationID
WHERE InspectionDatTime > EventDateTime
And something to do with ROW_NUMBER() = 1
I do not believe the results because for each widget there are multiple locations yet when I use this logic in relation to the inspections I get the same location every time. This is possible, but not probable as each partly built widget usually gets an inspection when they arrive and another at least one more some time later in the process. Sometimes it could be just the one though.
As it relates to the query I also have a problem with duplicate inspections occurring at the same exact date and time probably an anomaly I have to check into but I there is a prebuilt function that could presumably give me the information I wanted but again I do not believe it. It takes a TVP as the argument and inserting into the TVP variable forbids duplicate WidgetIDs and DateTimes. It is generic function and could be used to return a location for anything that has a widgetID and a datetime.
Hope this is explanatory enough.