SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER LogoffCleanup 
   ON  OfficertrackINg
   AFTER INSERT,DELETE
AS 
BEGIN

    SET NOCOUNT ON;

SELECT MAX(OfficerTrackINgId) as OfficertrackINgID  INTO #tempOT
FROM OfficerTrackINg 
WHERE LogoffDate is not NULL 
GROUP BY EmployeeId, dateadd(millisecond, -datepart(MILLISECOND, LogoffDate), LogoffDate) 
HAVING COUNT(*) > 1
SELECT max(UnitTrackINgId) as UnitTrackINgID  INTO #tempUT
FROM UnitTrackINg 
WHERE LogoffDate is not NULL 
GROUP BY UnitId, dateadd(millisecond, -datepart(MILLISECOND, LogoffDate), LogoffDate) 
HAVING COUNT(*) > 1
SELECT max(DepartmentVehicleTrackINgId) as DepartmentVehicleTrackINgId  INTO #tempDV
FROM DepartmentVehicleTrackINg
WHERE LogoffDate is not null
GROUP BY DepartmentVehicleId, dateadd(millisecond, -datepart(MILLISECOND, LogoffDate), LogoffDate) 
HAVING COUNT(*) > 1
DELETE FROM DepartmentVehicleTrackINg WHERE DepartmentVehicleTrackINgId IN (SELECT DepartmentVehicleTrackINgId FROM #tempDV)
DELETE FROM OfficerTrackINg WHERE OfficerTrackINgId IN (SELECT OfficerTrackINgId FROM #tempOT)
DELETE FROM UnitTrackINg WHERE UnitTrackINgId IN (SELECT UnitTrackINgID FROM #tempUT)


DROP TABLE #tempOT

DROP TABLE #tempUT

DROP TABLE #tempDV


END