Join on date range with same date column

Question

Good day. I have two tables in a database. I want to join them but I seem to be retrieving too many rows than needed. Neither table have any relationship other than a date range.

If table b's date falls within table a's date; then join. However, I seem to be joining way to many than needed.

How do I attach the username from table B that is the nearest date range from table A's? Any help is most appreciated.

Table A

dateS   auditmessage
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0
2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:48.290 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:48.290 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0
2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5
2018-04-09 07:06:51.380 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5
2018-04-09 07:08:31.737 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0
2018-04-09 07:08:31.737 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1
2018-04-09 07:08:34.977 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0
2018-04-09 07:08:34.977 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 1) changed from 0 to 2
2018-04-09 09:25:15.967 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 3) changed from 2 to 0
2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0
2018-04-09 09:25:15.967 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 1) changed from 0 to 2
2018-04-09 09:25:19.247 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 3) changed from 2 to 0
2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0
2018-04-09 09:25:19.247 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5
2018-04-09 12:32:30.123 Phase 1) (Setup) Parameter 3) changed from 0 to 2
2018-04-09 12:32:32.253 Phase 1) (Setup) Parameter 3) changed from 0 to 2

Table B

userName       DateS
sam     2018-04-09 07:11:45.907
andy    2018-04-09 09:16:41.023
carl    2018-04-09 12:17:45.057

My Failed Query

SELECT                   

           k.dates,
           k.auditmessage,
           l.username  
FROM       tableA k 
INNER JOIN tableB l 
           --  on l.dateS >=k.dateS --and l.dateS <= k.dateS 
           --where l.dateS >=k.dateS and l.dateS<=k.dateS

Desired Results

dateS   auditmessage   User
    2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0 sam
    2018-04-09 07:06:48.290 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5  sam
    2018-04-09 07:06:48.290 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5    sam
    2018-04-09 07:06:48.290 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5   sam
    2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 3) changed from 8.0 to 5.0 sam
    2018-04-09 07:06:51.380 Phase 2) (Heat Up) Parameter 24) changed from 15.0 to 14.5  sam
    2018-04-09 07:06:51.380 Phase 3) (Sterilization) Parameter 24) changed from 15.0 to 14.5    sam
    2018-04-09 07:06:51.380 Phase 4) (Air Pressurization) Parameter 11) changed from 15.0 to 14.5   sam
    2018-04-09 07:08:31.737 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0  sam
    2018-04-09 07:08:31.737 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1 sam
    2018-04-09 07:08:34.977 Phase 1) (Setup) Parameter 18) changed from 6.0 to 7.0  sam
    2018-04-09 07:08:34.977 Phase 4) (Air Pressurization) Parameter 20) changed from 0 to 1 sam
    2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 1) changed from 0 to 2   andy
    2018-04-09 09:25:15.967 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5   andy
    2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0  andy
    2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 3) changed from 2 to 0   andy
    2018-04-09 09:25:15.967 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0  andy
    2018-04-09 09:25:15.967 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5  andy
    2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 1) changed from 0 to 2   andy
    2018-04-09 09:25:19.247 Phase 4) (Air Pressurization) Parameter 11) changed from 14.5 to 17.5   andy
    2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 6.0 to 5.0  andy
    2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 3) changed from 2 to 0   andy
    2018-04-09 09:25:19.247 Phase 1) (Setup) Parameter 18) changed from 7.0 to 6.0  andy
    2018-04-09 09:25:19.247 Phase 2) (Heat Up) Parameter 24) changed from 14.5 to 17.5  andy
    2018-04-09 12:32:30.123 Phase 1) (Setup) Parameter 3) changed from 0 to 2   carl
    2018-04-09 12:32:32.253 Phase 1) (Setup) Parameter 3) changed from 0 to 2   carl

Answer 1

You can search for the nearest row with outer apply. For each row in TableB, it orders the TableA rows based on distance, then picks the first:

select  *
from    TableB b
outer apply
        (
        select  top 1 *
        from    TableA a
        order by
                abs(b.dateS - a.dateS)
        ) a