Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
Hi I have one table like below format:
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 21-Feb-13 3:28am
Edited 21-Feb-13 4:20am
CHill6059.4K
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try Following query..
 
Select ID, Date, Frequency from Table_Name where Date between '01/06/2009') and '01/06/2014' group by ID, Frequency
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 6,417
1 Sergey Alexandrovich Kryukov 5,344
2 Maciej Los 3,434
3 Peter Leow 3,259
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100