Need help selecting a record between two date ranges?

I was trying to select a record between two date ranges but I keep getting duplicate record when two date range overlaps as shown below.

Here is an example.

Policy Info

Policy #	Policy Effective Date	Policy termination date	 Year
001			2018-10-01				2019-10-01				 2018
002			2019-10-01				2020-10-01				 2019
003			2020-10-01				2021-10-01				 2020
004			2021-10-01				2022-10-01				 2022

Policy Limit

LimitID	  Effective Date	Termination Date	Limit
1	      2018-10-01		2021-10-01			 1000
2	      2018-10-01		3000-01-01			 2500

How can I select Limit ID: 1 for Policy #: 001,002 003 or for the years 2018, 2019, 2020 and for any policy effective date greater than 2021-01-01 use Limit ID = 2

I tried the following but it keeps creating dupicate

((limit.effective_from_date < policy.effective_to_date
  AND limit.effective_to_date > policy.effective_from_date
  )
  OR 
  
  (limit.effective_from_date = policy.effective_from_date 
  AND limit.effective_to_date = CONVERT(datetime, '01/01/3000', 102)))

but the above condition creates a duplicate. Is there any effective way of selecting a record within overlapping date ranges.

Any help will be appreciated!

Your problem is that you have overlapping periods for Policy Limits and you need to choose one. For what I understand from your data and I'm inferring a lot, you need to get the first limit for the FIRST period that it's [Policy Limit].[Effective Date] is earlier than the [Policy Info].[Policy Effective Date]
while [Policy Limit].[Termination Date] is later than [Policy Info].[Policy Termination Date].

If all my guessing is correct, you can do something like

drop table if exists #PolicyInfo 

drop table if exists #PolicyLimit
CREATE TABLE #PolicyInfo (
Policy INT,
Policy_Effective_Date DATE,
Policy_termination_date DATE,
[Year] int
)
CREATE TABLE #PolicyLimit(
LimitID INT,
Effective_Date DATE,
Termination_Date DATE,
Limit INT
)

INSERT INTO #PolicyInfo (Policy, Policy_Effective_Date, Policy_termination_date, [Year])
VALUES
    (001, '2018-10-01', '2019-10-01', 2018),
    (002, '2019-10-01', '2020-10-01', 2019),
    (003, '2020-10-01', '2021-10-01', 2020),
    (004, '2021-10-01', '2022-10-01', 2022)

INSERT INTO #PolicyLimit (LimitID, Effective_Date, Termination_Date, Limit)
VALUES 
    (1, '2018-10-01','2021-10-01',1000),
    (2, '2018-10-01','3000-01-01',2500)

;with cte AS (
    -- Join PolicyInfo with PolicyLimit
    -- condition: Policy_Effective_Date are between Effective_Date, pl.Termination_Date
    --            AND 
    --            Policy_Termination_Date are between Effective_Date, pl.Termination_Date
    SELECT *, 
        -- rank with partion by Policy
        ROW_NUMBER() OVER (PARTITION BY [pi].Policy ORDER BY pl.Effective_Date, pl.Termination_Date) rn
    FROM #PolicyInfo [pi]
    INNER JOIN #PolicyLimit pl ON 
        [pi].Policy_Effective_Date BETWEEN pl.Effective_Date AND pl.Termination_Date
        AND [pi].Policy_termination_date BETWEEN pl.Effective_Date AND pl.Termination_Date
)
SELECT Policy, LimitID
FROM cte
WHERE rn = 1 -- Select the first Limit per partition