I don't understand why your sample result set only includes two entries. There are three changes in frequency so I think there should be four entries in the result set.
I created a Stored Procedure that yielded this result set
1 2014-01-06 Half-Yearly
1 2012-01-06 Monthly
1 2011-01-06 Half-Yearly
1 2009-01-06 Monthly
Here is the Stored Procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RETRIEVER]
AS
BEGIN
Declare @curId Int,@curDate Date, @curFrequency varchar(50)
Declare @lastId Int,@lastDate Date, @lastFrequency varchar(50)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
DECLARE @SummarizedRowsTable TABLE (Id int,Date Date,Frequency varchar(50))
Declare myRows CURSOR FOR
SELECT id,date,frequency from testtable order by id,date,frequency
DECLARE @FIRST_TIME BIT
SET @FIRST_TIME=1
OPEN myRows
-- Get first row
FETCH NEXT FROM myRows INTO @curId,@curDate,@curFrequency
WHILE @@FETCH_STATUS = 0
BEGIN
IF @FIRST_TIME=1
BEGIN
SET @FIRST_TIME=0
-- Save the current row data
SET @lastId=@curId
SET @lastDate=@curDate
SET @lastFrequency=@curFrequency
END
ELSE
BEGIN
IF not (@curId=@lastId AND @curFrequency=@lastFrequency )
BEGIN
-- Insert data into the temporary table
INSERT INTO @SummarizedRowsTable (id,date,frequency) Values (@lastId, @lastDate, @lastFrequency);
-- Save the current row data
SET @lastId=@curId
SET @lastDate=@curDate
SET @lastFrequency=@curFrequency
END
END
FETCH NEXT FROM myRows INTO @curId,@curDate,@curFrequency
END
-- Insert the last row into the temporary table
INSERT INTO @SummarizedRowsTable (id,date,frequency) Values (@lastId, @lastDate, @lastFrequency);
CLOSE myRows;
DEALLOCATE myRows;
--
-- Return the temporary table as the stored procedure result
SELECT id,date,frequency FROM @SummarizedRowsTable order by id,date desc;
END
Execute the Stored Procedure in SQL Server Management Studio with this statement
exec RETRIEVER
go