Calculate the rolling time since an event for each record in SQL

Question

I am working on a query to calculate the time since the latest preventative maintenance (PM) for each piece of equipment on a rolling calendar. The goal is build a dataframe to analyze when a repair will take place.

Below is a table of the query results that I have with the added column "Time Since Last PM" which is the column that I want.

Call this Table 1:

EquipmentNumber |Year   |weeknumber |Current Week Repair|Time Since Last PM  
2069186         |2018   |10         |1                  |5  
2069186         |2018   |21         |1                  |1  
1626930         |2018   |09         |1                  |21  
1626930         |2019   |03         |1                  |15  

The preventative maintenance data comes from a query/table that is set up like the following i.e. Table 2.

Equipment Number |Year  |WeekNumber  
2069186          |2018  |5  
2069186          |2018  |20  
1626930          |2017  |40  
1626930          |2018  |40  

So I need to make sure that for my final query, Table 1, the “Time Since Last PM” for equipment 2069186 the first record is difference from week 5 2018 in Table 2 to week 10 2018. For the second record of equipment 2069186 the “Time Since Last PM” should be the difference between week 21 2018 in Table 1 and week 20 2018 in Table 2. For equipment 1626930 the first record should be the difference from week 9 2018 in Table 1 to week 40 2017 in Table 2. For equipment 1626930 the second record should be the difference from week 3 2019 in Table 1 to week 40 2018 in Table 2. Keep in mind that I want the difference between the current record in Table 1 and the latest PM prior to the year and week in Table 1.

The queries for Table 1 and Table 2 are very basic.

 SELECT   
 DISTINCT EquipmentNumber,
 Year, 
 weeknumber,
 [Current Week Repair]
 FROM TableA

 SELECT 
 EquipmentNumber,
 DatePart(Year, Created) as Year,
 DatePart(Week, Created) as WeekNumber
 FROM TableB

Any suggestions on the proper way to join the two tables and make the calculation? Or is there a way to utilize temp tables and variables that anyone can suggest? Any input would be appreciated!

Answer 1

You can use APPLY:

SELECT t1.*, t1.WeekNumber - ca.WeekNumber
FROM t1
OUTER APPLY (
    SELECT TOP 1 Year, WeekNumber
    FROM t2
    WHERE Equipment_Number = t1.Equipment_Number
    AND (
        (Year < t1.Year)
        OR
        (Year = t1.Year AND WeekNumber < t1.WeekNumber)
    )
    ORDER BY Year DESC, WeekNumber DESC
) AS ca

Calculating week number difference will be tricky and for that you'll need the number of weeks in the previous year.