May 26, 2016 at 11:21 am
Hopefully someone can provide some assistance:
What I need to know is when each driver had their first accident, and if they went to driving shcool I need to know that date, finally if they had another accident after they went to driving shcool what were those dates.
DriverIDFirstAccidentDateDrivingSchoolDate (if attended) SecondAccidentDate(after attended driving school)ThirdAccidentDate (after attended driving school) - etc...
CREATE TABLE tblDrivers (DriverID Int, AccidentDate date)
INSERT INTO tblDrivers(DriverID,AccidentDate)
VALUES (1,'03/01/2012'),
VALUES (1,'05/06/2015'),
VALUES (2,'08/05/2013'),
VALUES (3,'12/01/2011'),
VALUES (4,'06/22/2005'),
VALUES (4,'04/01/2008'),
VALUES (4,'11/21/2010')
GO
CREATE TABLE tblDrivingSchool (DriverID Int, ClassDate date)
INSERT INTO tblDrivers(DriverID,AccidentDate)
VALUES (1,'03/15/2012'),
VALUES (3,'12/18/2011'),
VALUES (4,'07/10/2005')
GO
May 26, 2016 at 11:47 am
Thanks for the setup stuff. But in the future please make sure it works. 😀
Note I don't think this code will work correctly if a driver has more than 2 accidents or more than one driving class. You will need to further restrict the rows for those cases.
CREATE TABLE #tblDrivers (DriverID Int, AccidentDate date)
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (1,'03/01/2012')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (1,'05/06/2015')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (2,'08/05/2013')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (3,'12/01/2011')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (4,'06/22/2005')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (4,'04/01/2008')
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (4,'11/21/2010')
GO
CREATE TABLE #tblDrivingSchool (DriverID Int, ClassDate date)
INSERT INTO #tblDrivingSchool(DriverID,ClassDate)
VALUES (1,'03/15/2012')
INSERT INTO #tblDrivingSchool(DriverID,ClassDate)
VALUES (3,'12/18/2011')
INSERT INTO #tblDrivingSchool(DriverID,ClassDate)
VALUES (4,'07/10/2005')
GO
SELECT * FROM #tbldrivers
SELECT * FROM #tbldrivingschool
SELECT d.DriverID,
d.accidentdate AS FirstAccidentDate,
sch.ClassDate AS DrivingClassDate,
acc2.accidentdate AS SecondAccidentDate
FROM #tbldrivers d
LEFT JOIN #tblDrivingSchool AS sch
ON d.driverid = sch.driverid AND sch.classdate > d.accidentdate
LEFT JOIN #tbldrivers AS acc2
ON d.driverid = acc2.driverid AND acc2.accidentdate > d.accidentdate
AND acc2.accidentdate > sch.classdate
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 26, 2016 at 11:57 am
Thanks, sorry for the bad scripting on my part. I'll see what I can work out
May 26, 2016 at 12:48 pm
For up to 3 accidents total, it's relatively straightforward, with a single table pass for each table -- afaik, beyond 3 would require additional code/complexity (at least one additional join/select back to #tblDrivers).
SELECT
d.DriverID,
MAX(d.FirstAccidentDate) AS FirstAccidentDate,
MIN(CASE WHEN ds.ClassDate >= d.FirstAccidentDate THEN ds.ClassDate END) AS DrivingSchoolDate,
MAX(d.SecondAccidentDate) AS SecondAccidentDate,
MAX(d.ThirdAccidentDate) AS ThirdAccidentDate
FROM (
SELECT
DriverID,
MAX(FirstAccidentDate) AS FirstAccidentDate,
CASE WHEN MAX(AccidentCount) = 1 THEN NULL
WHEN MAX(AccidentCount) = 2 THEN MAX(LastAccidentDate)
WHEN MAX(AccidentCount) = 3 THEN MAX(MiddleAccidentDate)
ELSE NULL END AS SecondAccidentDate,
CASE WHEN MAX(AccidentCount) = 1 THEN NULL
WHEN MAX(AccidentCount) = 2 THEN NULL
WHEN MAX(AccidentCount) = 3 THEN MAX(LastAccidentDate)
ELSE NULL END AS ThirdAccidentDate
FROM (
SELECT DriverID,
MIN(AccidentDate) AS FirstAccidentDate,
CAST(DATEADD(DAY, 0, SUM(DATEDIFF(DAY, 0, AccidentDate)) - DATEDIFF(DAY, 0, MIN(AccidentDate)) - DATEDIFF(DAY, 0, MAX(AccidentDate))) AS date) AS MiddleAccidentDate,
MAX(AccidentDate) AS LastAccidentDate,
COUNT(*) AS AccidentCount
FROM #tblDrivers
GROUP BY DriverID
) AS d2
GROUP BY DriverID
) AS d
LEFT OUTER JOIN #tblDrivingSchool ds ON ds.DriverID = d.DriverID
GROUP BY d.DriverID
Edit: Refined the SQL code slightly.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 30, 2016 at 3:05 am
This could help you...
CREATE TABLE #tblDrivers (DriverID Int, AccidentDate date)
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (1,'03/01/2012'),
(1,'05/06/2015'),
(2,'08/05/2013'),
(3,'12/01/2011'),
(4,'06/22/2005'),
(4,'04/01/2008'),
(4,'11/21/2010')
GO
CREATE TABLE #tblDrivingSchool (DriverID Int, ClassDate date)
INSERT INTO #tblDrivingSchool (DriverID,ClassDate)
values
(1,'03/15/2012'),
(3,'12/18/2011'),
(4,'07/10/2005')
;
With
Driver_Accident_History as
( Select
a.DriverID
,a.AccidentDate
,ROW_NUMBER() over (partition by a.DriverID order by a.AccidentDate) as Accident_Number
from #tblDrivers a
group by
a.DriverID
,a.AccidentDate)
Select
a.DriverID
,a.Accident_Number
,a.AccidentDate
,count(b.ClassDate) as Driving_classes_Attended_Prior_To_Accident
,max(b.ClassDate) as Last_Driving_classes_Prior_To_Accident
,count(c.ClassDate) as Driving_classes_Attended_After_Accident
,min(c.ClassDate) as First_Driving_classes_After_Accident
from Driver_Accident_History a
left join #tblDrivingSchool b on a.DriverID = b.DriverID and b.ClassDate <= a.AccidentDate
left join #tblDrivingSchool c on a.DriverID = c.DriverID and c.ClassDate > a.AccidentDate
Group by
a.DriverID
,a.Accident_Number
,a.AccidentDate
Drop table #tblDrivers,#tblDrivingSchool
May 30, 2016 at 3:08 am
This could help point you in correct direction, regardless of the number of accidents
CREATE TABLE #tblDrivers (DriverID Int, AccidentDate date)
INSERT INTO #tblDrivers(DriverID,AccidentDate)
VALUES (1,'03/01/2012'),
(1,'05/06/2015'),
(2,'08/05/2013'),
(3,'12/01/2011'),
(4,'06/22/2005'),
(4,'04/01/2008'),
(4,'11/21/2010')
GO
CREATE TABLE #tblDrivingSchool (DriverID Int, ClassDate date)
INSERT INTO #tblDrivingSchool (DriverID,ClassDate)
values
(1,'03/15/2012'),
(3,'12/18/2011'),
(4,'07/10/2005')
;
With
Driver_Accident_History as
( Select
a.DriverID
,a.AccidentDate
,ROW_NUMBER() over (partition by a.DriverID order by a.AccidentDate) as Accident_Number
from #tblDrivers a
group by
a.DriverID
,a.AccidentDate)
Select
a.DriverID
,a.Accident_Number
,a.AccidentDate
,count(b.ClassDate) as Driving_classes_Attended_Prior_To_Accident
,max(b.ClassDate) as Last_Driving_classes_Prior_To_Accident
,count(c.ClassDate) as Driving_classes_Attended_After_Accident
,min(c.ClassDate) as First_Driving_classes_After_Accident
from Driver_Accident_History a
left join #tblDrivingSchool b on a.DriverID = b.DriverID and b.ClassDate <= a.AccidentDate
left join #tblDrivingSchool c on a.DriverID = c.DriverID and c.ClassDate > a.AccidentDate
Group by
a.DriverID
,a.Accident_Number
,a.AccidentDate
Drop table #tblDrivers,#tblDrivingSchool
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply