Select record on every 'n' years-CodePudding

I need to return records that are equals to every 'n' years.

Table definition:

ID::int	CreatedDate::date	interval::int
1	1/1/2020	2

Now I want to return this record if it's every 2 year of a given date. A select statement something like this:

SELECT * FROM dbo.RecurringExpenses  WHERE CreatedDate is every 2 year of 1/1/2021 (return NO record)
    
SELECT * FROM dbo.RecurringExpenses  WHERE CreatedDate is every 2 year of 1/1/2022 (return the record)
    
SELECT * FROM dbo.RecurringExpenses  WHERE CreatedDate is every 2 year of 1/1/2023 (return NO record)
    
SELECT * FROM dbo.RecurringExpenses  WHERE CreatedDate is every 2 year of 1/1/2024 (return the record)
    
SELECT * FROM dbo.RecurringExpenses  WHERE CreatedDate is every 2 year of 1/1/2025 (return NO record)

and so on...

CodePudding user response：

You can use the modulo(%)

SELECT * FROM (
SELECT '01-01-2026' AS y ,2 AS I
UNION ALL
SELECT '01-01-2022' AS y ,2 AS I
UNION ALL
SELECT '01-01-2023' AS y ,2 AS I
UNION ALL
SELECT '01-01-2024' AS y ,2 AS I
UNION ALL
SELECT '01-01-2025' AS y ,2 AS I
) AS TB WHERE (DATEPART(year ,y) - DATEPART(YEAR ,GETDATE()))%I = 0