• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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.

The basic query is below.

SELECT        TOP (100) PERCENT crm.SocialSecurity, eq.UnitNumber, crm.EmployeeCode, crm.FirstName, crm.MiddleName, crm.LastName, ad.FleetManager, 
                         ad.FleetOpsManager
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

Open in new window


A screenshot of the table erd is below.

erd
0
Steve Hougom
Asked:
Steve Hougom
  • 26
  • 10
  • 2
2 Solutions
 
Ess KayEntrapenuerCommented:
DECLARE @yearsNoAccident int = 7 --lets say 7 years

SELECT        TOP (100) PERCENT crm.SocialSecurity, eq.UnitNumber, crm.EmployeeCode, crm.FirstName, crm.MiddleName, crm.LastName, ad.FleetManager,
                         ad.FleetOpsManager
FROM            rtiCompany.dbo.tbCMPRSCResourceMaster AS crm LEFT 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


WHERE DATEDIFF(year,a.AccidentDateTime ,getdate())  > @yearsNoAccident
ORDER BY a.AccidentDateTime DESC


---------------------------------------------------------

For Months:



DECLARE @monthsNoAccident int = 7 --lets say 7 months

SELECT        TOP (100) PERCENT crm.SocialSecurity, eq.UnitNumber, crm.EmployeeCode, crm.FirstName, crm.MiddleName, crm.LastName, ad.FleetManager,
                         ad.FleetOpsManager
FROM            rtiCompany.dbo.tbCMPRSCResourceMaster AS crm LEFT 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


WHERE DATEDIFF(month,a.AccidentDateTime ,getdate())  > @monthsNoAccident
ORDER BY a.AccidentDateTime DESC




------------------------------------
Alternitavely, if you like to send a parameter as a choice





DECLARE @numberNoAccident As int = 7 --lets say 7
DECLARE @SEARCH As int = 1   --IF 1 SEARCH BY YEAR, IF 2 SEARCH BY MONTH

SELECT        TOP (100) PERCENT crm.SocialSecurity, eq.UnitNumber, crm.EmployeeCode, crm.FirstName, crm.MiddleName, crm.LastName, ad.FleetManager,
                         ad.FleetOpsManager
FROM            rtiCompany.dbo.tbCMPRSCResourceMaster AS crm LEFT 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


WHERE (DATEDIFF(month,a.AccidentDateTime ,getdate())  > @numberNoAccident AND @SEARCH = 2) OR (DATEDIFF(YEAR,a.AccidentDateTime ,getdate())  > @numberNoAccident AND @SEARCH = 1)

ORDER BY a.AccidentDateTime DESC
0
 
Scott PletcherSenior DBACommented:
-- use either/both: for example, for 3 yrs 6 mos, use 3y & 6m, or NULL/0y and 42m
DECLARE @years_since_accident int
DECLARE @months_since_accident int

DECLARE @first_accident_date datetime

-- 3 and 1/2 years, for example:
SET @years_since_accident = 3
SET @months_since_accident = 6

SET @first_accident_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -
    (ISNULL(@years_since_accident, 0) * 12 + ISNULL(@months_since_accident, 0)), 0)


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
            )
    )
0
 
Steve HougomDeveloper IIAuthor Commented:
That is pretty awesome.  Testing it out now.  Thanks!
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Steve HougomDeveloper IIAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
>> 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)
0
 
Steve HougomDeveloper IIAuthor Commented:
Hi Scott!

Im assuming you left this out by accident(ha accident lol)

Declare @first_accident_date = date?

Also in this case all the drivers with an accident are in accident driver and all employees including drivers and non drivers are in tbCMPRSCResourceMaster ie crm.

To clarify the goal of the query is to reward drivers who have gone the longest without any type of accident so its like x award for 2 years 0 months, x award for 1 year 5 months since last accident.  

Also the following join should be:
LEFT OUTER JOIN Accident.tbAccident AS a ON
        a.Id= ad.AccidentId

vs

LEFT OUTER JOIN Accident.tbAccident AS a ON
        a.AccidentId = ad.AccidentId



Hope that helps.
0
 
Steve HougomDeveloper IIAuthor Commented:
Result of first run is as shows.  I got the dreaded "having clause" error.

not in select list err
0
 
Steve HougomDeveloper IIAuthor Commented:
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.  

test
0
 
Scott PletcherSenior DBACommented:
Typo, I left the GROUP BY in the inner query, please remove it; the GROUP BY isn't needed when using ROW_NUMBER().
0
 
Steve HougomDeveloper IIAuthor Commented:
Ok.

Also i played around with this and changed the IS NULL to IS NOT NULL and so I see the affect...it shows the last accident date.  The original is a list of those without accidents.

Interesting.  This is a good stub for something more perhaps.

tried not null just to see
0
 
Steve HougomDeveloper IIAuthor Commented:
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.

drivercode is not null
0
 
Scott PletcherSenior DBACommented:
>> This brought down the number of rows quite a bit by ridding non-drivers. <<

That got rid of no accidents, not non-drivers.  I don't see a "master" table of all drives.  It doesn't seem as if crm.EmployeeCode would be only drivers.


>> 1 yr, 2 yr, 3yr and including month <<

1/2/3 yr is easy enough, but I'm not sure what you mean by "and including month"
0
 
Steve HougomDeveloper IIAuthor Commented:
The crm table is all employees drivers and non-drivers.  

The acccident table is all accident records.  The accidentdriver table is a record of drivers that joins to the accidents table.

I suppose that because you're doing a left outer join from crm to accidentdriver table that would eliminate all non-drivers from crm.  

Here is the erd for crm and accidentdriver.  They join up via EmployeeCode to DriverCode.

crm
accidentdriver
Regarding the layout of showing years/months.  Im trying to show the breakdown of those drivers who have gone the longest without an accident.

so for example.

DriverCode   Name        YearsSafe  MonthsSafe
AcKJ               Ackermn      1                 8
0
 
Scott PletcherSenior DBACommented:
>> I suppose that because you're doing a left outer join from crm to accidentdriver table that would eliminate all non-drivers from crm. <<

Not really.  How do I tell whether a given employee in the crm table is for a driver or not, if you want to report only on drivers?
0
 
Steve HougomDeveloper IIAuthor Commented:
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.
0
 
Steve HougomDeveloper IIAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
Interesting ... and somewhat bizarre.  So you can't report on a driver at all unless they've had at least one accident?!  Or is there a "dummy" accident row added when the driver is first added??
0
 
Ess KayEntrapenuerCommented:
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
0
 
Steve HougomDeveloper IIAuthor Commented:
Scott,

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.
0
 
Scott PletcherSenior DBACommented:
What if a driver *never* had an accident?  How would you pull his/her record, since he/she presumably would not be in the AccidentDriver table at all?


>> drivers table but that table doesnt contain drivercode  <<

Does it contain some other code than can like to the crm table, and then a left join to driveraccident?
0
 
Steve HougomDeveloper IIAuthor Commented:
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.

accident tables
0
 
Steve HougomDeveloper IIAuthor Commented:
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.

Thanks now I think I understand better.
0
 
Steve HougomDeveloper IIAuthor Commented:
I found a way I think Scott.  Just check to see if  driverfileId is or is not null.  Im still looking into this but that should help get drivers who have never had any accidents.

driverfileId
check for null
0
 
Steve HougomDeveloper IIAuthor Commented:
Never mind i looked and crm.DriverFileId......every single row looks to be null.
0
 
Steve HougomDeveloper IIAuthor Commented:
The correct way to get only drivers is to join from accidentdriver.driverid to rtidriver.tbDMDriverFile.  I just use the DriverCode to lookup the fname and lname from crm on employee code.

tbDMDriverFile
0
 
Steve HougomDeveloper IIAuthor Commented:
OK final try here.  This is how we get all drivers accidents or not.  By the id of the crm(companyresourcemaster table).

Select * from rticompany..tbcmprscresourcemaster  rm
Left join roehl.accidents.accidentdriver acd on acd.driverid = rm.id

Scott are you still interested in helping?  Thanks
0
 
Steve HougomDeveloper IIAuthor Commented:
Changed it to this.  Now just trying to figure out how to display in years/months since accident tiers/groups.

Declare @first_accident_date  datetime = GetDate();

SELECT  acc.driverId,crm.LastName
    , acc.Last_Accident_DateTime, acc.Last_Accident_FleetManager
    , acc.Last_Accident_FleetOpsManager
	, acc.DriverCode
FROM rtiCompany.dbo.tbCMPRSCResourceMaster AS crm --?table_with_all_drivers?
LEFT OUTER JOIN (
    SELECT 
        ad.DriverId, 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.Id = ad.AccidentId
  
		
) AS acc ON
    acc.DriverId = crm.Id AND
    acc.row_num = 1
WHERE
    (acc.Last_Accident_DateTime IS NULL OR 
     acc.Last_Accident_Datetime < @first_accident_date)
	 and acc.DriverCode is not null
	 order by Last_Accident_DateTime asc

Open in new window

0
 
Scott PletcherSenior DBACommented:
Yes, I can still work on this, but it will be a couple of hours.
0
 
Steve HougomDeveloper IIAuthor Commented:
Ok Scott whenever you get time I really appreciate your help your awesome.
0
 
Steve HougomDeveloper IIAuthor Commented:
Updated.  Have to filter on crm.Inactive = 0 or you get inactive emp/drivers

Declare @first_accident_date  datetime = GetDate();

SELECT  acc.driverId,crm.LastName
    , acc.Last_Accident_DateTime, acc.Last_Accident_FleetManager
    , acc.Last_Accident_FleetOpsManager
	, acc.DriverCode
FROM rtiCompany.dbo.tbCMPRSCResourceMaster AS crm --?table_with_all_drivers?
LEFT OUTER JOIN (
    SELECT 
        ad.DriverId, 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.Id = ad.AccidentId
  
		
) AS acc ON
    acc.DriverId = crm.Id AND
    acc.row_num = 1
WHERE
    (acc.Last_Accident_DateTime IS NULL OR 
     acc.Last_Accident_Datetime < @first_accident_date)
	 and acc.DriverCode is not null
	 and crm.Inactive = 0
	 order by Last_Accident_DateTime asc

Open in new window

0
 
Scott PletcherSenior DBACommented:
DECLARE @first_accident_date datetime
--3y&6m ago, as an example
SET @first_accident_date = DATEADD(MONTH, -(3*12+6), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

SELECT
      df.DriverId
    , crm.LastName
    , acc.Last_Accident_DateTime
    , acc.Last_Accident_FleetManager
    , acc.Last_Accident_FleetOpsManager
    , acc.DriverCode
    , DATEDIFF(MONTH, acc.Last_Accident_Datetime, GETDATE()) / 12 AS YearsSafe
    , DATEDIFF(MONTH, acc.Last_Accident_Datetime, GETDATE()) % 12 AS MonthsSafe
FROM dbo.tbDMDriverFile df
LEFT OUTER JOIN rtiCompany.dbo.tbCMPRSCResourceMaster AS crm ON
    crm.Id = df.DriverId
LEFT OUTER JOIN (
    SELECT
        ad.DriverId, 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.Id = ad.AccidentId
) AS acc ON
    acc.DriverId = df.DriverId AND
    acc.row_num = 1
WHERE
    (acc.Last_Accident_DateTime IS NULL --never had an acccident
     OR (acc.Last_Accident_Datetime < @first_accident_date AND acc.DriverCode IS NOT NULL)) AND
    crm.Inactive = 0
ORDER BY
    Last_Accident_DateTime ASC
0
 
Steve HougomDeveloper IIAuthor Commented:
Ok I just need to figure out while im getting all the NULLS.


nulls
0
 
Steve HougomDeveloper IIAuthor Commented:
I could attach 4 small sample xls files of data.  

a
ad
df
crm


Like 100 rows each.  Would that help any or is that not the way to go?

See ERD once again below.
erd
0
 
Steve HougomDeveloper IIAuthor Commented:
I was able to make this work.

think this is what i need
0
 
Steve HougomDeveloper IIAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for hougie40's comment #a40032296

for the following reason:

My final query was the closest to an acceptable solution.  There were some very good samples provided by others though.  I thank all for your help.
0
 
Scott PletcherSenior DBACommented:
Wow, seriously?  Good luck with future qs!
0
 
Steve HougomDeveloper IIAuthor Commented:
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.
0
 
Steve HougomDeveloper IIAuthor Commented:
scotts awesome.  The best.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 26
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now