I need help writing a query that checks an accident table to get a set of drivers who have NOT had an accident since x number of years or months. The accident table has accident records and the accidentdriver table has those drivers who were part of the accident.
It would be really nice to be able to pass in a parameter like in number of years or months since last accident and get back rows of those that havent had an accident in 3,5,7 years for example.
Years probably being most important but a months one as a second version might be nice.
The basic query is below.
SELECT TOP (100) PERCENT crm.SocialSecurity, eq.UnitNumber, crm.EmployeeCode, crm.FirstName, crm.MiddleName, crm.LastName, ad.FleetManager,
FROM rtiCompany.dbo.tbCMPRSCResourceMaster AS crm INNER JOIN
Accident.tbAccidentDriver AS ad ON crm.EmployeeCode = ad.DriverCode INNER JOIN
rtiEquipment.dbo.tbEQUnitMaster AS eq ON ad.DriverCode = eq.Driver1Code INNER JOIN
Accident.tbAccident AS a ON ad.AccidentId = a.Id
ORDER BY a.AccidentDateTime DESC
A screenshot of the table erd is below.