Solved

help with sql query

Posted on 2014-04-25
39
246 Views
Last Modified: 2014-05-26
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
Comment
Question by:hougie40
  • 26
  • 10
  • 2
39 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40023061
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40023109
-- 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
 

Author Comment

by:hougie40
ID: 40023112
That is pretty awesome.  Testing it out now.  Thanks!
0
 

Author Comment

by:hougie40
ID: 40023550
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40023685
>> 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
 

Author Comment

by:hougie40
ID: 40023709
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
 

Author Comment

by:hougie40
ID: 40023740
Result of first run is as shows.  I got the dreaded "having clause" error.

not in select list err
0
 

Author Comment

by:hougie40
ID: 40023773
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40023784
Typo, I left the GROUP BY in the inner query, please remove it; the GROUP BY isn't needed when using ROW_NUMBER().
0
 

Author Comment

by:hougie40
ID: 40023805
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
 

Author Comment

by:hougie40
ID: 40023821
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40024086
>> 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
 

Author Comment

by:hougie40
ID: 40024532
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40027156
>> 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
 

Author Comment

by:hougie40
ID: 40027166
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
 

Author Comment

by:hougie40
ID: 40027196
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40027207
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40027216
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
 

Author Comment

by:hougie40
ID: 40027514
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
Highfive + Dolby Voice = No More Audio Complaints!

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.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40027543
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
 

Author Comment

by:hougie40
ID: 40027635
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
 

Author Comment

by:hougie40
ID: 40028130
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
 

Author Comment

by:hougie40
ID: 40028144
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
 

Author Comment

by:hougie40
ID: 40028334
Never mind i looked and crm.DriverFileId......every single row looks to be null.
0
 

Author Comment

by:hougie40
ID: 40028350
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
 

Author Comment

by:hougie40
ID: 40029536
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
 

Author Comment

by:hougie40
ID: 40029682
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40029716
Yes, I can still work on this, but it will be a couple of hours.
0
 

Author Comment

by:hougie40
ID: 40029731
Ok Scott whenever you get time I really appreciate your help your awesome.
0
 

Author Comment

by:hougie40
ID: 40029829
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40030168
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
 

Author Comment

by:hougie40
ID: 40030282
Ok I just need to figure out while im getting all the NULLS.


nulls
0
 

Author Comment

by:hougie40
ID: 40030557
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
 

Assisted Solution

by:hougie40
hougie40 earned 0 total points
ID: 40032296
I was able to make this work.

think this is what i need
0
 

Author Comment

by:hougie40
ID: 40081819
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40081641
Wow, seriously?  Good luck with future qs!
0
 

Author Comment

by:hougie40
ID: 40081820
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
 

Author Comment

by:hougie40
ID: 40081847
scotts awesome.  The best.
0

Featured Post

What is SQL Server and how does it work?

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.

Join & Write a Comment

     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…
This video discusses moving either the default database or any database to a new volume.
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…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now