Use ABS to Find the Closest Match

The ABS function in SQL Server is a little bit overlooked and unutilized. It returns the absolute, positive value of the given numeric expression.

One particular set of problems that ABS is very useful is when needing the find the closest match to a given value. That could be to find the closest ZIP code, date, etc. Below are two examples of utilizing ABS for this purpose.

-- Create table with sales regions
CREATE TABLE SalesRegions (
 zip_code INT PRIMARY KEY,
 region_name VARCHAR(35));
 
-- Insert sampel data
INSERT INTO SalesRegions VALUES(40320, 'North Area');
INSERT INTO SalesRegions VALUES(40324, 'North/East Area');
INSERT INTO SalesRegions VALUES(40326, 'North/West Area');
 
-- Define search zip code
DECLARE @search_zip INT;
SET @search_zip = 40323;
 
-- Find the closes region by zip code
SELECT TOP(1) zip_code, region_name
FROM SalesRegions
ORDER BY ABS(zip_code - @search_zip);
 
-- Results
zip_code    region_name
----------- ---------------
40324       North/East Area
 
 
-- Create Employee table
CREATE TABLE Employees (
 employee_nbr INT PRIMARY KEY,
 employee_name VARCHAR(35));
 
-- Employee evaluation dates
CREATE TABLE EmployeeEvaluations (
 employee_nbr INT,
 evaluation_date DATETIME,
 PRIMARY KEY (employee_nbr, evaluation_date));
 
-- Sample data
INSERT INTO Employees VALUES(1, 'John Doe');
INSERT INTO Employees VALUES(2, 'Jeff Brown');
 
INSERT INTO EmployeeEvaluations VALUES(1, '20070101');
INSERT INTO EmployeeEvaluations VALUES(1, '20080101');
INSERT INTO EmployeeEvaluations VALUES(1, '20080304');
INSERT INTO EmployeeEvaluations VALUES(2, '20080604');
 
-- Find the closest evaluation date, could be in the future
SELECT A.employee_nbr, A.employee_name, B.evaluation_date
FROM Employees AS A
CROSS APPLY
(SELECT TOP(1) B.evaluation_date
 FROM EmployeeEvaluations AS B
 WHERE A.employee_nbr = B.employee_nbr
 ORDER BY ABS(DATEDIFF(DAY, B.evaluation_date, CURRENT_TIMESTAMP))) AS B;
 
-- Results
employee_nbr employee_name  evaluation_date
------------ -------------- -----------------------
1            John Doe       2008-03-04 00:00:00.000
2            Jeff Brown     2008-06-04 00:00:00.000