Home Forums SQL Server 2008 T-SQL (SS2K8) Need help identifying dates of an occurance after a specific date RE: Need help identifying dates of an occurance after a specific date

  • 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.