SQL - Select Closest Preceding Date
<p>I have a database table that contains one or more entries for each patient. These contain free text and additional information about a test request. Querying on a patient would for example return:-</p>

<pre><code>TestID      PatientID   RequestMade             FreeText
1           23           13/12/2015 11:00:00    Feeling breathless
1125        23           07/04/2016 09:31:15    Unexplained fractures
2556        23           04/12/2016 16:20:21    Check liver function – on statins
</code></pre>

<p>When viewing test results I have to pull up the request information relating to the test which will be the last one prior to the test. The results have a TestDate so a TestDate of '13/04/2016 14:21:30' should display the request of '07/04/2016 09:31:15'. I am unsure how to code this efficiently as returning every entry for a patient and doing a date comparison on each one seems not the best way to tackle it.</p>