Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have one table like below format:
CSS
ID            Date                     Frequency
1            01/06/2009               Monthly
1            01/06/2010               Monthly
1            01/06/2011               Half-Yearly
1            01/06/2012               Monthly           *
1            01/06/2013               Monthly
1            01/06/2014               Half- Yearly     *


I want records like below:
Current  Frequency   : 1            01/06/2014               Half- Yearly
Previous  Frequency  : 1            01/06/2009               Monthly


For ID=1,frequency is "Monthly" for 01/06/2009.This will be change on 01/06/2011 etc..

Current Effective date is the date the Frequency changed : 01/06/2014 Half- Yearly
previous effective date must be the date that that frequency was changed : 01/06/2009 Monthly

I am using SQL Server 2000.Please provide solution.
Posted
Updated 21-Feb-13 4:20am
v3

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
 
Share this answer
 
v2
Try Following query..

VB
Select ID, Date, Frequency from Table_Name where Date between '01/06/2009') and '01/06/2014' group by ID, Frequency
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900