获取表的行之间的值更改 [英] Get the value change between rows of a table

问题描述

您好我有一张如下格式的表：

  ID  日期 频率 
  1  01/06/2009 每月 
  1  01/06/2010 每月 
  1  01/06/2011 半年 
  1  01/06/2012 每月  *  
  1  01/06/2013 每月 
  1  01/06/2014 一半 -   每年  *  

我想要如下记录：

当前频率：1 01 / 06/2014半年
上一个频率：1 01/06/2009每月

如果ID = 1，频率为月刊为01/06/2009。这将于2011年6月1日更改等。



当前生效日期是频率变更的日期：01 / 06/2014半年

之前的生效日期必须是该频率更改的日期：01/06/2009每月



我正在使用SQL Server 2000.Please提供解决方案。

解决方案

我不明白为什么你的样本结果集只包含两个条目。频率有三个变化，所以我认为结果集中应该有四个条目。



我创建了一个存储过程，产生了这个结果集

 1 2014-01-06半年度
 1 2012-01- 06每月
 1 2011-01-06半年
 1 2009-01-06每月

这里是存储过程

 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 CREATE PROCEDURE [dbo]。[RETRIEVER] 
 AS 
 BEGIN 
声明@curId Int，@ curDate Date，@ curFrequency varchar（50）
声明@lastId Int，@ lastDate Date，@ lastFrequency varchar（50）
  -  SET NOCOUNT ON添加以防止
的额外结果集 - 干扰SELECT语句。 
 SET NOCOUNT ON; 
  -  
 DECLARE @SummarizedRowsTable TABLE（Id int，Date Date，Frequency varchar（50））
声明myRows CURSOR FOR 
 SELECT id，date，frequency from testtable order by id ，日期，频率
 DECLARE @FIRST_TIME BIT 
 SET @ FIRST_TIME = 1 
 OPEN myRows 
  - 获取第一行
 FETCH NEXT from myRows INTO @ curId，@ curDate ，@ curFrequency 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
 IF @ FIRST_TIME = 1 
 BEGIN 
 SET @ FIRST_TIME = 0 
  - 保存当前行数据
 SET @ lastId = @curId 
 SET @ lastDate = @ curDate 
 SET @ lastFrequency = @ curFrequency 
 END 
 ELSE 
 BEGIN 
如果不是（@ curId = @ lastId AND @curFrequency = @ lastFrequency）
 BEGIN 
  - 将数据插入临时表
 INSERT INTO @SummarizedRowsTable（id，date，frequency）值（ @lastId，@ lastDate，@ lastFrequency）; 
  - 保存当前行数据
 SET @ lastId = @ curId 
 SET @ lastDate = @ curDate 
 SET @ lastFrequency = @ curFrequency 
 END 
 END 
 FETCH NEXT from myRows INTO @ curId，@ curDate，@ curFrequency 
 END 
  - 将最后一行插入临时表
 INSERT INTO @SummarizedRowsTable（id，date，频率）值（@lastId，@ lastDate，@ lastFrequency）; 
关闭myRows; 
 DEALLOCATE myRows; 
  -  
  - 返回临时表作为存储过程结果
 SELECT id，date，frequency FROM @SummarizedRowsTable order by id，date desc; 
 END 

使用此语句执行SQL Server Management Studio中的存储过程

 exec RETRIEVER 
 go 

尝试以下查询..

 选择 ID，日期，来自Table_Name的频率，其中日期在'  01/06/2009'）和'01 / 06/2014'组之间按ID，频率 

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.

解决方案

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

Try Following query..

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

这篇关于获取表的行之间的值更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！