Query formula for "one year later" (1 Viewer)

[timo1999]

I have two tables, one shows the every time a machine was worked on since the year 2000 to present (planned maintenance), and the other table shows every time a machine broke down since the year 2000 (Forced). I can link on the machine name because they are identical in both tables.

I want the query to show me the machines that broke down within a years time after being worked on by comparing the "date" variable from both tables (dtmStartOfMaint) from the table "planned maintenance" and (dtmStartOfOutage) from the "forced" table.

What is the best way to write a formula, that will calculate the two variables to let me see only the machines that failed within the one years time frame?

 

[jal]

SELECT P.MachineName, dtmStartOfMaint , dtmStartOfOutage, dtmStartOfOutage - dtmStartOfMaint as NumDays
FROM PlannedMaintenance as P
INNER JOIN Forced as F
ON P.MachineName = F.MachineName
WHERE dtmStartOfOutage - dtmStartOfMaint < 365

or if you want to select all the columns


SELECT *, dtmStartOfMaint , dtmStartOfOutage, dtmStartOfOutage - dtmStartOfMaint as NumDays
FROM PlannedMaintenance as P
INNER JOIN Forced as F
ON P.MachineName = F.MachineName
WHERE dtmStartOfOutage - dtmStartOfMaint < 365

 

[timo1999]

Thank you very much, this works great!