help with sql query

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.

SELECT crm.column1, crm.column2 --, crm...
FROM rtiCompany.dbo.tbCMPRSCResourceMaster AS crm --?table_with_all_drivers?
WHERE NOT EXISTS(
SELECT ad.DriverCode
FROM Accident.tbAccidentDriver AS ad
WHERE
ad.DriverCode = crm.EmployeeCode AND
ad.AccidentId IN (
SELECT a.AccidentId
FROM Accident.tbAccident AS a
WHERE
a.AccidentDateTime >= @first_accident_date
)
)

So apparently the requirement is to show the Total Number of Years and Total Months without an acccident for a driver. It would be cool to have a version that just looks for those drivers without an accident and shows the years and months since last accident date.

Is that a major change? The queries above can be used in other ways but the main goal of the query is to just sort by the drivers who have gone the longest without an accident.

If that goes above what I originally asked for then I can close this one out and make a new one. Im happy with above queries but my goal is to satisfy the requirement.

>> that just looks for those drivers without an accident and shows the years and months since last accident date. <<

I assume you mean "without an accident within the time range specified", as otherwise that's self-contradictory :-) .

No need to open a new q, the new request is close enough.

I think we can change the query to show the last accident date while also verifying that only people without an accident in at least nn months ago are shown at all, something like this (hopefully). If the "Last_Accident_*" columns are NULL, then the driver has never had an accident.

I think I used ">=" on the date comparison above when it should be "<", which I've adjusted below. Naturally please verify you're getting back the rows you expect.

SELECT crm.column1, crm.column2 --, crm...
, acc.Last_Accident_DateTime, acc.Last_Accident_FleetManager
, acc.Last_Accident_FleetOpsManager
FROM rtiCompany.dbo.tbCMPRSCResourceMaster AS crm --?table_with_all_drivers?
LEFT OUTER JOIN (
SELECT
ad.DriverCode, a.AccidentDateTime AS Last_Accident_DateTime,
ad.FleetManager AS Last_Accident_FleetManager,
ad.FleetOpsManager AS Last_Accident_FleetOpsManager,
ROW_NUMBER() OVER(PARTITION BY ad.DriverCode ORDER BY a.AccidentDateTime DESC) AS row_num
FROM Accident.tbAccidentDriver AS ad
LEFT OUTER JOIN Accident.tbAccident AS a ON
a.AccidentId = ad.AccidentId
GROUP BY
ad.DriverCode
) AS acc ON
acc.DriverCode = crm.EmployeeCode AND
acc.row_num = 1
WHERE
(acc.Last_Accident_DateTime IS NULL OR
acc.Last_Accident_Datetime < @first_accident_date)

Ran this to "make it happy" Took out firstname to protect the innocent. Found some typos. But i wonder why all the nulls. I didnt know what to set @accident_date parm at so i just chose today since criteria is < @accident_date.

This brought down the number of rows quite a bit by ridding non-drivers. See addition to where clause. still trying to figure out a way to show these grouped sequentially by 1 yr, 2 yr, 3yr and including month would be the cats meow.

There is a driver table but it doesnt contain the all important driver code. I was going on the idea that crm(employee master table) joins to accidentdriver which only contains drivers anyway on from crm.EmployeeCode to accidentdriver.DriverCode so that would be good enough especially if an outer join is done to only show rows on the crm side where there is a matching accidentdriver. Meanwhile Scott I will investigate to see if I can find a better way to join to a driver table. Thanks for letting me know your thoughts.

Im finding out that the way we have it crm.EmployeeCode to accidentdriver.drivercode is the only way via outer join to grab the drivers first and last name. Which does need to be shown. So it is what it is.

Alternitavely,
add a ISDriver column to the employees, find out who is driver and use that in the criterea. My query would take all employees who have no accident,
Add the isdriver = true and you got yourself an answer

No there is a Driver database with a drivers table in it but that table doesnt contain drivercode which i need to use to join to the company resource master to get the drivers first name and last name. And in order to get that i need to use driver code from accidentdriver. Thats it. So unfortunately there would need to be an outer join from crm to accidentdriver but if a join is done on accidentdriver.drivercode to crm.employeecode your only going to get those that match so whats the issue? Maybe im not following.

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

This is for an accident website so we dont care about anything but accidents. The only reason the company employee master gets pulled in is to grab there first and last name thats the only place in the company its stored. We have other systems that keep track of all drivers this one just tracks accident. We have a rather large dba team here so there on it.

We have lots of databases and lots of systems. This one just happens to be for accidents and it has ALOT of FK dependencies.

See attached to get an idea of how many tables are just in accident system.

Hmmm. I think i might understand what you are saying. What if there was a driver that just never had an accident...ever. Hmm. Wow. Ok. I might need to re-think this and find a way to get to over to our driver table or find another way in crm to id a driver.

Requesting moderator review perhaps I didnt grade this correctly. I just wanted to close out the question because I decided to go another direction. Certainly wasnt to offend any one. Perhaps its just my ignorance about the rules on here? Im not sure.

Featured Post

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…

Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…