SQL Server find rows approaching a service day and month

I have a table which lists equipment with an installation date. These items need to be serviced annually or every five years. So the rows have a service interval of 1 or 5. Something like:

CREATE TABLE Equipment(
    Guid uniqueidentifier,
    InstallationDate datetime,
    ServiceInterval tinyint
);

What I need to do is return a list of equipment two months before they are due for service

For simplicity Let’s assume I have these installation dates and they all have a service interval of 1 which means an annual service is required.

2008-1-17
2011-4-28
2012-5-13
2020-9-6
2006-12-2

I need to return from this list those which (ignoring the year) have a date and month that falls into the next two months. So the first and last items require service. The first item requires service in January and the last in December.

I’m not sure the best way to approach this problem. Sure this must be a common requirement but I can’t find any examples to learn from. I can easily find dates between ranges but struggling to work this one out.

Can someone point me in the right direction please?

Update: 15/11/2020

After some thought and playing with the suggestions other have made I came up with the following script. I realised that most of this equipment is fire extinguishers and these need to be serviced every year. They also need extended services every 5 and 10 years. I need a list of assets that are coming up for any of these services.

select top 20
    MONTH(InstallationDate) as InstallMonth,
    Day(InstallationDate) as InstallDay,
    format(InstallationDate,'MMM dd yyyy') as Installed,
    case WHEN DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate)) < getdate()
    then dateadd(year,1, DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate)) )
    else   DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate))
    end as ServiceDue,
    case when year(InstallationDate) + 5 = year(getdate()) then '5 year service due' 
    when year(InstallationDate) + 5 < year(getdate()) then '5 year service passed' 
    when year(InstallationDate) + 5 > year(getdate()) then 'Five year service due ' + cast(year(InstallationDate) + 5 as varchar)
    end as '5 Year Service',

    case when year(InstallationDate) + 10 = year(getdate()) then '10 year service due' 
    when year(InstallationDate) + 10 < year(getdate()) then '10 year service passed' 
    when year(InstallationDate) + 10 > year(getdate()) then 'Ten year service due ' + cast(year(InstallationDate) + 10 as varchar)
    end as '10 Year Service'

    
from equipment e    

order by
case WHEN DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate)) < getdate()
    then dateadd(year,1, DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate)) )
    else   DATEFROMPARTS(year(getdate()),month(InstallationDate),day(InstallationDate))
    end asc

Answer

The upcoming 2 month date window could be calculated using GETDATE as CURR_START_DT as well as EOMONTH(GETDATE(), 2) as CURR_END_DT. The date window as of today is CURR_START_DT =’2020-11-13′ and CURR_END_DT=’2021-01-31′. Since the year doesn’t matter, and since the date window may span more than 1 year, the CALC_DT is created using the YEAR from the CURR_START_DT and the PLUS_CALC_DT is created using the YEAR from the CURR_END_DT. Something like this

Declare table and query

declare @YourTable              table(InstallationDate      date);  

insert into @YourTable values 
 ('2008-1-17')
,('2011-4-28')
,('2012-5-13')
,('2020-9-6')
,('2006-12-2');

select yt.InstallationDate, dfp.calc_dt, dfp_plus.calc_dt as plus_calc_dt, 
       dfp.dt as curr_start_dt, dfp.eom as curr_end_dt 
from @YourTable yt
     cross apply
     (select cast(getdate() as date) dt, eomonth(getdate(), 2) eom,
             datefromparts(year(getdate()),
                           month(yt.InstallationDate), 
                           day(yt.InstallationDate)) calc_dt) dfp
     cross apply
     (select datefromparts(year(dfp.eom),
                           month(yt.InstallationDate), 
                           day(yt.InstallationDate)) calc_dt) dfp_plus
where dfp.calc_dt between dfp.dt and dfp.eom
      or
      dfp_plus.calc_dt between dfp.dt and dfp.eom;

Output

InstallationDate    calc_dt     plus_calc_dt    curr_start_dt   curr_end_dt
2008-01-17          2020-01-17  2021-01-17      2020-11-13      2021-01-31
2006-12-02          2020-12-02  2021-12-02      2020-11-13      2021-01-31