Select certain row if it is in the date range otherwise take fallback

Question

SQL Server 2017

The table contains duplicate contracts per factory with different From and To dates.

I'd like to select the correct row based on a declared YearMonth parameter. If this falls in the range between From and To date this row should be selected otherwise the fallback row with the default date 199401 should be chosen.

Table Scheme:

CREATE TABLE [TABLE_1] 
(
FACILITY varchar(1) Null,
CONTRACT int NULL,
DATE_FROM int Null,
DATE_TO int Null,

);

INSERT INTO TABLE_1 (FACILITY, CONTRACT,DATE_FROM,DATE_TO)
VALUES ('A',123, 202111, 202112),
   ('A',123, 199401, 199401),
   ('B',333, 202001, 202006),
   ('B',333, 199401, 199401);

DB-Fiddle

My desired result at YearMonth = 202111

FACILITY	CONTRACT	DATE_FROM	DATE_TO
A	123	202111	202112
B	333	199401	199401

My desired result at YearMonth = 202002

FACILITY	CONTRACT	DATE_FROM	DATE_TO
A	123	199401	199401
B	333	202001	202006

My desired result at YearMonth = 202209

FACILITY	CONTRACT	DATE_FROM	DATE_TO
A	123	199401	199401
B	333	199401	199401

I came up with this working solution:

DECLARE @YEAR_MONTH AS INT = 202111

SELECT *
FROM (
    SELECT FACILITY, CONTRACT, DATE_FROM, DATE_TO,
        ROW_NUMBER() OVER 
        (
            PARTITION BY CONTRACT ORDER BY 
            CASE WHEN @YEAR_MONTH BETWEEN DATE_FROM AND DATE_TO THEN 1 ELSE 2 END, DATE_FROM
        ) AS [ROW_NR]
    FROM Table_1 
) AS a
WHERE a.ROW_NR = 1      

I wonder if there is a better, shorter approach here. Thanks.

Answer 1

I would urge you to reconsider this YearMonth column. It is really awful. It can't be validated without a lot of effort. It should be a date datatype. Or if you really need Year and Month it should be two columns, not one. Shoving those two values into a single tuple violates 1NF. Datatypes are crucial and that seems like a real problem to maintain.