Find the nearest time from another table and add column Please help. I need to create a view of Assembly details and when they have gone through a particular machine. I have two tables. Table 1 has the main assembly data and Table 2 has the machine data. 

The final query has all Table 1 fields and the Machine field of Table 2 where the BuildDate (Table 1) is = to the WaveDate (Table 2) and the Wavetime (Table 2) is less than and nearest to the BuildTime (Table 1). 

Hopefully the pictures make more sense than my description :/ Any help is greatly appreciated. Many thanks
This answer might contain some typing erros, and is untested, because i cannot test from a picture... 

SELECT
   AssemblyNo,
   SerialNo,
   AssemblyType,
   DefectType,
   BuildDate,
   BuildTime,
   x.Machine
FROM Table1 t1
CROSS APPLY (
    SELECT TOP 1 t2.Machine
    FROM Table2 t2
    WHERE t1.BuildDate=t2.WaveDate AND t2.Wavetime &lt;=t1.BuildTime) x