Salary Ranges: Match Overlapping Intervals in SQL Server

Salary ranges match when a job budget and a candidate expectation share at least one acceptable amount. Comparing only one endpoint misses useful matches. Use both boundaries, and define what missing values mean.

Compare both boundaries of salary ranges

Suppose a job offers 60,000 to 80,000 annually. A candidate asking for 75,000 to 95,000 shares the interval from 75,000 to 80,000. A request starting at 90,000 has no overlap.

For finite intervals, require JobMin <= CandidateMax and CandidateMin <= JobMax. These comparisons include an interval that contains the other. They also accept two intervals touching at exactly one amount.

Choose that endpoint rule deliberately. Inclusive comparisons treat 80,000 to 80,000 as a possible agreement. To require positive shared width, compare the computed overlap end with its start.

Keep the salary basis and missing-value meaning explicit

Store amounts in the same currency and pay period before comparing them. Annual salary, monthly salary and hourly pay need an agreed conversion basis. The sample uses nonnegative annual amounts in one currency.

Here, a missing endpoint means an intentionally unrestricted bound. It does not mean an unanswered question. Unknown expectations need clarification, rather than automatic eligibility.

The OR conditions make that open-bound rule visible. Ordinary comparisons with NULL do not establish a match. The result retains missing endpoints without presenting invented salary figures.

Run a small boundary example

This SQL Server 2012 or later example creates one temporary table. Its nine cases cover partial overlap, containment, equality, endpoint contact, gaps and open bounds. It then tries to insert a negative amount and a reversed range, and shows what happens.

-- Salary ranges, one currency and annual pay basis.
-- NULL means an intentionally unrestricted endpoint, never an unknown answer.
DROP TABLE IF EXISTS #SalaryCases;
CREATE TABLE #SalaryCases
(
    CaseId int NOT NULL PRIMARY KEY,
    Shape varchar(24) NOT NULL,
    JobMin decimal(12,2) NULL, JobMax decimal(12,2) NULL,
    CandidateMin decimal(12,2) NULL, CandidateMax decimal(12,2) NULL,
    CHECK (JobMin IS NULL OR JobMin >= 0),
    CHECK (JobMax IS NULL OR JobMax >= 0),
    CHECK (CandidateMin IS NULL OR CandidateMin >= 0),
    CHECK (CandidateMax IS NULL OR CandidateMax >= 0),
    CHECK (JobMin IS NULL OR JobMax IS NULL OR JobMin <= JobMax),
    CHECK (CandidateMin IS NULL OR CandidateMax IS NULL
           OR CandidateMin <= CandidateMax)
);
INSERT #SalaryCases VALUES
  (1,'Partial overlap',60000,80000,75000,95000),
  (2,'Contains job',60000,80000,50000,100000),
  (3,'Equal intervals',60000,80000,60000,80000),
  (4,'Touching endpoints',60000,80000,80000,90000),
  (5,'Gap below',60000,80000,10000,50000),
  (6,'Gap above',60000,80000,90000,110000),
  (7,'Open lower bound',60000,80000,NULL,85000),
  (8,'Open upper bound',60000,80000,75000,NULL),
  (9,'Both bounds open',60000,80000,NULL,NULL);

SELECT s.CaseId, s.Shape, s.JobMin, s.JobMax,
       s.CandidateMin, s.CandidateMax, m.RangesMatch,
       CASE WHEN m.RangesMatch = 1
                 AND s.JobMin IS NOT NULL AND s.JobMax IS NOT NULL
                 AND s.CandidateMin IS NOT NULL
                 AND s.CandidateMax IS NOT NULL
            THEN (CASE WHEN s.JobMax < s.CandidateMax
                       THEN s.JobMax ELSE s.CandidateMax END)
               - (CASE WHEN s.JobMin > s.CandidateMin
                       THEN s.JobMin ELSE s.CandidateMin END)
       END AS FiniteOverlapAmount
FROM #SalaryCases AS s
CROSS APPLY
(
    SELECT CASE WHEN
      (s.JobMin IS NULL OR s.CandidateMax IS NULL
       OR s.JobMin <= s.CandidateMax)
      AND
      (s.CandidateMin IS NULL OR s.JobMax IS NULL
       OR s.CandidateMin <= s.JobMax)
      THEN 1 ELSE 0 END AS RangesMatch
) AS m
ORDER BY s.CaseId;

DECLARE @Validation table
  (TestName varchar(24), ErrorNumber int, Outcome varchar(20));
BEGIN TRY
    INSERT #SalaryCases VALUES
      (10,'Invalid negative',60000,80000,-1,50000);
    INSERT @Validation VALUES ('Negative amount',NULL,'Accepted');
END TRY
BEGIN CATCH
    INSERT @Validation VALUES ('Negative amount',ERROR_NUMBER(),'Rejected');
END CATCH;
BEGIN TRY
    INSERT #SalaryCases VALUES
      (11,'Invalid reversed',60000,80000,90000,70000);
    INSERT @Validation VALUES ('Reversed bounds',NULL,'Accepted');
END TRY
BEGIN CATCH
    INSERT @Validation VALUES ('Reversed bounds',ERROR_NUMBER(),'Rejected');
END CATCH;
SELECT TestName,ErrorNumber,Outcome FROM @Validation;

DROP TABLE #SalaryCases;

Read the overlap result without overpromising

Look at FiniteOverlapAmount. The widths are 5,000, 20,000, 20,000 and zero for the first four cases. The gap cases do not qualify. Open-bound cases qualify under the stated rule, while their finite width remains NULL.

Width measures the shared numeric interval. It does not measure experience, working hours or the likelihood of accepting an offer. A salary match is one eligibility condition in a larger hiring decision.

Testing only CandidateMin BETWEEN JobMin AND JobMax misses the containment case. Its candidate minimum is below the job minimum, although its interval covers the entire budget. Both boundary comparisons are necessary.

Apply the rule to a larger matching query

Use the same two conditions when joining actual job and candidate tables. Apply relevant business filters before matching every possible pair. Inspect the execution plan and reads with representative data.

If you rank finite matches by width, specify a stable tie breaker. Keep unrestricted intervals in a separately defined ranking policy. An artificial substitute for infinity must not become somebody’s stated compensation expectation.

Small examples like this make the edge cases easy to trust.

A salary match is not a hiring decision, it is one shared range.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.