使用第一行的偏移列来计算sql 2008中的下一行 [英] Use offset column of first row to calculate next row in sql 2008

问题描述

Hello Everyone！

需要一个MS Sql查询来解决下面的场景

Hello Everyone !
Need a MS Sql query to solve below scenario

Create table Projection
AnyPrimaryKey int
DateOfAppearance datetime,
Offset int  --- this is offset to calculate next projected date

 INSERT INTO Projection(AnyPrimaryKey,DateOfAppearance,Offset)values(1,25th July 2013,4)
 INSERT INTO Projection(AnyPrimaryKey,DateOfAppearance,Offset)values(2,NULL,4)
 INSERT INTO Projection(AnyPrimaryKey,DateOfAppearance,Offset)values(3,NULL,4)
 INSERT INTO Projection(AnyPrimaryKey,DateOfAppearance,Offset)values(4,NULL,4)
 INSERT INTO Projection(AnyPrimaryKey,DateOfAppearance,Offset)values(5,NULL,4)

假设此表已预填充wi第一行为DateOfAppearance = 2013年7月25日，偏移为4（天）。然后计算下一行，公式是DATEADD（day，Offset，DateOfAppearance）。所以下一行变为2,29thJulty2013,4。第三排将于2013年8月3日。使用前一行中的偏移来构造每一行。



我想在SQl中为所有列执行此查询，即使用offset和DateOfAppearance列计算预计日期在它之前的行。我不想为此使用游标。



提前致谢

Assume this table is prepopulated with 1st row as DateOfAppearance= 25th July 2013 and offset as 4(days). then to calculate next row , formula is DATEADD(day,Offset,DateOfAppearance).so next row becomes 2,29thJulty2013,4. and third row will be 3,2nd August,2013. Each row is contructed using offset from a row before it.

I want a query which does this in SQl for all the columns i.e calculate projected dates using offset and DateOfAppearance column of the row before it. I dont want to use cursors for this.

Thanks in advance

推荐答案

运行以下代码片段并检查输出。这是所希望的！

Run the following code snippet and check the output. It is as desired!

Create table #Projection
(
	SequenceNum int identity(1,1),
	DateOfAppearance datetime,
	Offset int  --- this is offset to calculate next projected date
)

INSERT INTO #Projection(DateOfAppearance,Offset)values('25 July 2013',4)
INSERT INTO #Projection(DateOfAppearance,Offset)values(NULL,4)
INSERT INTO #Projection(DateOfAppearance,Offset)values(NULL,4)
INSERT INTO #Projection(DateOfAppearance,Offset)values(NULL,4)
INSERT INTO #Projection(DateOfAppearance,Offset)values(NULL,4)

--select * from #Projection

declare @maxSequenceNum int
declare @sequenceNumCtr int
declare @previousDate datetime
declare @newDate datetime
declare @currentOffset int

set @maxSequenceNum = (select MAX(SequenceNum) from #Projection)
set @sequenceNumCtr = 1

while @sequenceNumCtr <= @maxSequenceNum
begin
	if @sequenceNumCtr = 1
	begin
		-- fetch date from table only if it is first record
		set @previousDate = (select DateOfAppearance from #Projection where SequenceNum = 1)	
	end
	else
	begin
		set @currentOffset = (select Offset from #Projection where SequenceNum = @sequenceNumCtr)
		set @newDate = DATEADD(day, @currentOffset, @previousDate)

		update #Projection set DateOfAppearance = @newDate where SequenceNum = @sequenceNumCtr
	
		set @previousDate = @newDate -- for next record
	end
	set @sequenceNumCtr += 1
end

select * from #Projection
drop table #Projection

SequenceNum DateOfAppearance    Offset
1   2013-07-25 00:00:00.000 4
2   2013-07-29 00:00:00.000 4
3   2013-08-02 00:00:00.000 4
4   2013-08-06 00:00:00.000 4
5   2013-08-10 00:00:00.000 4

请注意我使用临时表仅用于演示目的。

Please note that I have used temporary table just for the demo purpose.

这篇关于使用第一行的偏移列来计算sql 2008中的下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！