Filtering table based on customerID and closest transaction date to unique dates

Question

Using Microsoft SQL Server Management Studio I am accessing a SQL Server table (TransHistory) that contains CustomerID and TransactionDate as my two fields of interest. Separately I have a list of CustomerIDs and Dates of Interest in an excel spreadsheet. I am trying to query the list of customers from excel spreadsheet in TransHistory where it outputs the closest past TransactionDate to the Date of Interest for that CustomerID.

TransHistory has hundreds of thousands of customers+transactions for which I'm querying for about a thousand customers and only need 1 unique row per customer. The Dates of Interest are unique to each customer (example entries from spreadsheet below) and won't always match the TransactionDate in TransHistory table, which is why I am querying for the closest past date to the Date of Interest.

CustomerID	Date of Interest
11111	1/21/2022
22222	2/28/2013
33333	5/13/2018

The thing is I know how to do it in a multistep process of first querying all data in TransHistory for my CustomerIDs and then doing a subsequent query to then filter for the Date of Interest. When trying to simply/combine it into 1 query I get stumped by the fact that Date of Interest is unique to each CustomerID.

Would it be possible to filter for CustomerID while also filtering for CustomerID specific Dates of Interest in the same query? I am thinking it will require a two-dimensional array as a where statement, but I've never worked with them and not sure how to begin setting this up. Would appreciate being pointed in the right direction. Thanks!

Answer 1

I would provide the server with "Date of Interest" data and let the SQL Sever do the job

select top(1) with ties t.*
from Dates_of_Interest d
join TransHistory t on t.CustomerID = d.CustomerID
order by row_number() over(partition by d.CustomerID order by abs(datediff(d, t.TransactionDate, d.Date_of_Interest)))