How to reduce the record on the basis of Time if the minimum 2 minutes duration in SQL Server 2014

i have table following data

(id, date ,time)

11 2015/8/1 12:20:00

11 2015/8/1 12:21:00

11 2015/8/1 18:05:20

12 2015/8/1 11:20:00

12 2015/8/1 11:21:00

12 2015/8/1 18:10:20

i need table following record only

(id, date ,time)

11 2015/8/1 12:20:00

11 2015/8/1 18:05:20

12 2015/8/1 11:20:00

12 2015/8/1 18:10:20

any suggestion anywhere me 

hi navaraj,

according thread title, in case, rows time less 2 minutes later previous row should excluded each id , date, right? please see below sample. stefan data.

declare @sample table     (       id int ,       [date] date ,       [time] time     );  insert  @sample values  ( 11, '2015/8/1', '12:20:00' ),         ( 11, '2015/8/1', '12:21:00' ),         ( 11, '2015/8/1', '18:05:20' ),         ( 12, '2015/8/1', '11:20:00' ),         ( 12, '2015/8/1', '11:21:00' ),         ( 12, '2015/8/1', '18:10:20' ); 
declare @sample table
    (
      id int ,
      [date] date ,
      [time] time
    );

insert  @sample
values  ( 11, '2015/8/1', '12:20:00' ),
        ( 11, '2015/8/1', '12:21:00' ),
        ( 11, '2015/8/1', '18:05:20' ),
        ( 12, '2015/8/1', '11:20:00' ),
        ( 12, '2015/8/1', '11:21:00' ),
        ( 12, '2015/8/1', '18:10:20' );

;with cte as(
select id,date,time,lag(time) over(partition id,date order time) nexttm @sample
)
select id,[date],[time] cte nexttm null or datediff(minute,nexttm,time)>2          

if have question, feel free let me know.

---

eric zhang
technet community support

SQL Server  >  SQL Server Data Access