Use offset column of first row to calculate next row in sql 2008

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

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

SQL

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)

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

Posted 25-Jul-13 5:25am

devcode007

Add a Solution

Comments

[no name] 25-Jul-13 11:32am

If you know the formula and all that, why can't you do this? What have you tried to do for yourself? What is wrong with the code that you have written?

devcode007 25-Jul-13 13:12pm

I have used cursor for this first.
Now i have written an update query to do this which is like
UPDATE t
SET t.DateOfAppearance=DATEADD(day,tmp.offset,tmp.DateOfAppearance)
FROM Projection t
INNER JOIN Projection tmp on tmp.SequenceNumber + 1 = t.SequenceNumber
This is making only second row updated properly but not the rest because for the rest of rows, the second row which is updated is not available for calculating 3rd and so forth.

Hope that makes sense now

devcode007 25-Jul-13 13:13pm

i have added SequenceNumber as SequenceNumber int IDENTITY(1,1).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

nikhil-vartak · Accepted Answer · 2013-07-25T11:54:00

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

CSS

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.