UPDATE t
SET t.PostDup=1
FROM table t
CROSS APPLY (SELECT TOP 1 End_Time
             FROM table
             WHERE EventID = t.EventID
             AND [Date] = t.[Date]
             AND userID = t.UserID
             AND Exp_Time < t.Exp_Time
             ORDER BY Exp_Time DESC)t1
WHERE t1.End_Time> t.Exp_time

UPDATE t
SET t.PostDup=1
FROM 
(SELECT PostDup,
Exp_Time,
LAG(End_Time) OVER (PARTITION BY EventID,UserID,[Date] ORDER BY Exp_Time) AS PrevEndTime
FROM table
)t
WHERE PrevEndTime > Exp_Time

DECLARE @Test table

(EventID varchar(20),
[Date] date,
Exp_Time time,
End_Time time,
New_Time time,
UserID int,
PostDup bit)

INSERT INTO @Test  (EventID,Date,Exp_Time,End_Time,New_Time,UserID)
VALUES ('B AAOCHI7','7/12/2012','8:09:21','8:09:29','8:11:21',7323314),
('B AAOCHI7','7/12/2012','8:09:23','8:09:31','8:11:23',7323314),
 ('B AAOCHI7','7/12/2012','8:31:37','8:31:47','8:33:37',7323314)
 		



 UPDATE t
SET t.PostDup=1
FROM @Test t
CROSS APPLY (SELECT TOP 1 End_Time
             FROM @Test
             WHERE EventID = t.EventID
             AND [Date] = t.[Date]
             AND userID = t.UserID
             AND Exp_Time < t.Exp_Time
             ORDER BY Exp_Time DESC)t1
WHERE t1.End_Time> t.Exp_time


select * from @test


output
-------------------------------------------------------
EventID	     Date	         Exp_Time	         End_Time	         New_Time	         UserID	PostDup
B AAOCHI7	     2012-07-12	08:09:21.0000000	08:09:29.0000000	08:11:21.0000000	7323314	NULL
B AAOCHI7	     2012-07-12	08:09:23.0000000	08:09:31.0000000	08:11:23.0000000	7323314	1
B AAOCHI7	     2012-07-12	08:31:37.0000000	08:31:47.0000000	08:33:37.0000000	7323314	NULL

UPDATE t
SET t .PosDup = 1
FROM AlerterAlarmAudible_Temp t CROSS APPLY
(SELECT TOP 1 End_Time
FROM AlerterAlarmAudible_Temp
WHERE EventID = t .TrainIDEventID AND [Date] = t.[Date] AND UserID = t.UserID AND Exp_Time < t.Exp_Time
ORDER BY EventID, Date, Exp_Time, UserID DESC) t1
WHERE t1.End_Time > t .Exp_time

quote:

---

Originally posted by batcater98

visa: You have been so helpful - let me put one more step to you. Now that I finally got all of the dups from the table I want to modify the query just a little to update and find potitial sequincial events based on the New_Start_Time of row1 and the End_Time of row2.

So in English Terms from same data structure above - But now say if End_Time(row2)<= New_Start_Time(row1) then update Seq_Alrm = 1 If not Seq_Alrm = NULL. And traverse throught the table row by row. Making sure EventID = t.EventID and Date = t.date and userid = t.userid.

Regards,
The Dark Knight
-Give What is Right, Not What is Left-

---