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