>>ID |Date |Rate >>1 |1/1/2007 |1 >>2 |1/2/2007 |1 >>3 |1/3/2007 |1 >>4 |1/4/2007 |1.25 >>5 |1/5/2007 |1.25 >>6 |1/6/2007 |1.5 >>7 |1/7/2007 |1 >>8 |1/8/2007 |1 >>9 |1/9/2007 |1 >>10 |1/10/2007 |1.25 >>11 |1/11/2007 |1.25 >>12 |1/12/2007 |1.25 >>13 |1/13/2007 |1.25 >>14 |1/14/2007 |1 >>15 |1/15/2007 |1 >>16 |1/16/2007 |1 >>>>
>>ID |Date |Rate >>1 |1/1/2007 |1 >>4 |1/4/2007 |1.25 >>6 |1/6/2007 |1.5 >>7 |1/7/2007 |1 >>10 |1/10/2007 |1.25 >>14 |1/14/2007 |1 >>>>
>DECLARE @Temp TABLE (Id int, Date datetime, Rate float) >INSERT INTO @Temp VALUES (1,'1/1/2007',1) >INSERT INTO @Temp VALUES (19,'1/2/2007',1) >INSERT INTO @Temp VALUES (3,'1/3/2007',1) >INSERT INTO @Temp VALUES (4,'1/4/2007',1.25) >INSERT INTO @Temp VALUES (5,'1/5/2007',1.25) >INSERT INTO @Temp VALUES (6,'1/6/2007',1.5) >INSERT INTO @Temp VALUES (7,'1/7/2007',1) >INSERT INTO @Temp VALUES (8,'1/8/2007',1) >INSERT INTO @Temp VALUES (9,'1/9/2007',1) >INSERT INTO @Temp VALUES (10,'1/10/2007',1.25) >INSERT INTO @Temp VALUES (11,'1/11/2007',1.25) >INSERT INTO @Temp VALUES (12,'1/12/2007',1.25) >INSERT INTO @Temp VALUES (13,'1/13/2007',1.25) >INSERT INTO @Temp VALUES (14,'1/14/2007',1) >INSERT INTO @Temp VALUES (15,'1/15/2007',1) >INSERT INTO @Temp VALUES (16,'1/16/2007',1) > >SELECT Tmp.* > FROM @Temp Tmp >LEFT JOIN @Temp t on Tmp.Date = t.Date + 1 > and Tmp.Rate = t.Rate >where t.ID is null >>Of course this query will work only if Dates are in sequence.