-- 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
Labels: t-sql programming