[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Update column based on value in previous row

Posted on 2003-12-04
Medium Priority
738 Views
Hi all,

Imagine I have the following rows in a table:

Start    End    Total
100      0        97
0          0        121
0          0        85
0          500    97

I need to populate all of these records so that the End column less the start column gives the total, e.g. this is how it shoud look:

Start    End     Total
100      197     97
197      318     121
318      403     85
403      500     97

Is there any way I can do this in a single line T-SQL?  If not, what's the most efficient way of doing this?

Thanks, Frantic.
0
Question by:Frantic_Orange
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 6
• 5
• +2

LVL 26

Expert Comment

ID: 9875123
There are a lot of methods to do that, from a single statement to a simple stored procedure using a cursor.
BUT
Is there a field we can rely on to determine the actual lines order

Do you have something like a datetime field or an incremental IDENTITY field ?

Hilaire
0

Author Comment

ID: 9875211

The actual order is determined by two other fields, an integer and a numeric (18,2).  Lines selected using both of these fields in ascending sequence will be given in the desired order.

There is also a unique IDENTITY field but that cannot be relied upon for sequencing.
0

LVL 26

Expert Comment

ID: 9875273

Table structure, fields for the ordering, ...
Thks
Hilaire
0

Author Comment

ID: 9875333
Table structure:

ID1 (int)   ID2 (numeric)   Start (int)   End (int)   Total (int)
1               3.05                100            0              97
1               4.10                0               0              121
2               1.06                0               0               85
2               9.10                0               500           97

The values in the ID1 and ID2 fields are irrelevant for the calculation, they are purely for sequencing.
0

LVL 4

Expert Comment

ID: 9875870
There is no way to do it in a single SQL statement (or if there is I have never figured it out.)

I would use a curser for the least confusing method.

declare @identfield int
declare @ID1 int
declare @id2 numeric
declare @start int
declare @end int
declare @total int
declare tblpop cursor for

while

0

LVL 4

Assisted Solution

szacks earned 200 total points
ID: 9876046
Sorry that went in the middle

declare @identfield int
declare @ID1 int
declare @id2 numeric
declare @start int
declare @end int
declare @total int
declare @oldend int
set @oldend=0
declare tblpop cursor for
select identfield,id1,id2,start,end,total from table1
order by id1,id2
for update

open tblpop
fetch next from tblpop into @identfield,@ID1,@ID2,@start,@end,@total
while @@fetch_status=0
begin
if @start=0
begin
@start=@oldend
end
@end=@start+@total

update table1 set start=@start,end=@end
where current of tblpop

set @oldend=@end
fetch next from tblpop into @identfield,@ID1,@ID2,@start,@end,@total
end
0

LVL 19

Expert Comment

ID: 9876221
@Frantic_Orange:

> Is there any way I can do this in a single line T-SQL?  If not, what's the
> most efficient way of doing this?

You do *NOT* need a cursor for this.  You can do it with 2 SQL statements.  In my example, the table is called "PrevValueTest", so you'll have to change that to reflect your table.  But here is the code that you need:
SELECT RowID = IDENTITY( int, 1, 1 ), * INTO #T FROM PrevValueTest ORDER BY ID1, ID2

UPDATE PrevValueTest
SET Start = NewValues.NewStart,
[End] = NewValues.NewStart + Total
FROM (SELECT ID1, ID2, NewStart = COALESCE((SELECT SUM(Total) FROM #T WHERE RowID < T.RowID) +
(SELECT Start FROM #T WHERE RowID = (SELECT MIN(RowID) FROM #T)), [Start])
FROM #T T) AS NewValues
WHERE
PrevValueTest.ID1 = NewValues.ID1
AND
PrevValueTest.ID2 = NewValues.ID2

DROP TABLE #T

The above code was tested and working on SQL2K, but it should work with SQL 7 too.

Hope That Helps,
Dex*
0

LVL 19

Expert Comment

ID: 9876234
@szacks:

> update table1 set start=@start,end=@end
> where current of tblpop

I didn't know you could do that!  That's neat!

Dex*
0

LVL 4

Expert Comment

ID: 9876637
That's some pretty neat code Dexstar., I've never used Coalesce before. but I'll certainly remember it.

The only comment I have on your code is you forgot that he already has an identity column, that is irrelevant to this function. So instead of using RowID=Identity(1,1),* you would have to use RowID=Identity(1,1),ID1,Id2,Start,[end],total
otherwise it crashes because the Identity field inherits from the original table and you can't have 2 identity fields
0

Author Comment

ID: 9876689
OK - thanks for the information.

@Dexstar: You *do* seem to get around a bit, don't you? ;)  Is there any reason that I *shouldn't* use a cursor for this?  Although your code looks fine, there may be other areas that I need to do something more complicated with each record and a cursor seems to lend itself to this kind of processing.
0

LVL 50

Assisted Solution

ID: 9876876
Cursor are perfectly fine for doing repetetive processing....

but you should if possible avoid there use when you are dealing with
situations which are just pure set manipulation...
cursors in SQL server are quite expensive in terms of processing requirements... (and other dbms's) .

They can however be much simplier to understand...

but they really come into there own if you want to perform complex actions
based on complex row retrieval criteria...
e.g. sending emails...
interfacing to other systems / applications

that's not to say you can't do those things via set operations and/or tempoaray tables....

;-)
finally someone else who uses coalesce...

ps coalesce is very similar to ISNULL  and is the reverse of NULLIF
its also similar to
case when a is not null then a else b  end

0

LVL 4

Expert Comment

ID: 9876880
cursors are slightly less efficient then using standard sql code (in SQL server, in Oracle, they are more efficient), but a very complicated statement can be less efficient then a cursor or multiple simple statements.
0

LVL 50

Expert Comment

ID: 9876932
yes code it how it feels best to you...
then if performance isn't acceptable
examine ways of making it so...

0

LVL 19

Expert Comment

ID: 9877185
@Frantic_Orange:  Heh, I go where the questions I can answer are.  :)  Any reason to NOT use a cursor?  Well, it depends on when/where you are going to be performing this operation.  If you were doing it inside of a trigger, then I would avoid cursors.  If you run it less frequently, then cursors will probably be fine, especially if the calculations get more complicated.  But unless they are crazy complicated, I bet we could still come up with a single update statement.

You just asked for a single T-SQL statement to do the update, and I wanted to give you as close to that as I could get.

@szachs:  Sure enough!  I didn't include the other IDENTITY field in my sample table.  When I added one, sure enough, it complained about having 2 IDENTITY fields.  The only change I would make in that case is that I would select that ID field instead of ID1 and ID2, and use that to join back to the original table.  And then you would only need to select the new IDENTITY, the old IDENTITY, Start, and Total (End is not used).  Like this:
SELECT RowID = IDENTITY( int, 1, 1 ), CAST([ID] AS INT) AS [ID], Start, Total INTO #T FROM PrevValueTest ORDER BY ID1, ID2

Anyway, thanks for pointing that out!

@lowfatspread:  How's it going, Man?  Yeah, not everyone is cool enough to use COALESCE.  :)  I always use it instead of ISNULL() because I never know when I am going to want to tack a 3rd value onto the end of it.  Also, I totally agree with your last point.  As they say:  "Beware the premature optimization"

Dex*

0

LVL 50

Expert Comment

ID: 9877376
thanks dex*
hadn't realized isnull   was just case when a is null then b else a end
not like coalesce at all....
0

LVL 19

Accepted Solution

Dexstar earned 200 total points
ID: 9877440
Just for posterity's sake, here is the updated query to account for the other identity field.  You would think it would be faster because it only has to join on 1 field instead of 2, but the difference seems to be negliable.  I don't know why.  I'm guessing there aren't really enough records in the table to make a a fair measurement.  :/

Dex*

SELECT RowID = IDENTITY( int, 1, 1 ), CAST([ID] AS INT) AS [ID], Start, Total INTO #T FROM PrevValueTest ORDER BY ID1, ID2

UPDATE PrevValueTest
SET Start = NewValues.NewStart,
[End] = NewValues.NewStart + Total
FROM (SELECT [ID], NewStart = COALESCE((SELECT SUM(Total) FROM #T WHERE RowID < T.RowID) + (SELECT Start FROM #T WHERE RowID = (SELECT MIN(RowID) FROM #T)), [Start]) FROM #T T) AS NewValues
WHERE
PrevValueTest.[ID] = NewValues.[ID]
0

LVL 4

Expert Comment

ID: 9880875
The problem with complex single statement queries is they can take a lot longer then multiple simple statements.
For example, I did a very complex query inlcuding left joins and sub statements etc.. and got the entire code to work, but it took an hour to complete.

A more experienced programmer couldn't figure out how exactly my code was working, but said if it was him he would have written 6 simple statements. When we tested his six statements (with me laughing the whole time because I did it in 1) it took 6 minutes to complete.

So I learned the hard way that the coolest statement isn't always the most efficient.
0

Author Comment

ID: 9881532
@Lowfatspread: I use coalesce as well - but I can't claim the credit for discovering it myself.  I actually use it after Dex* answered another question for me a while ago!

I'll check out both solutions - I *did* actually ask for a single T-SQL statement so if all tests OK then credits will go to Dex* but if he doesn't mind, I'll split some of the points out to Lowfatspread as his is the option I'll probably use.  The code isn't in a trigger, it's going to be run occasionally, say 20 times a day, on different data so a cursor would be fine.

I'll be back shortly - thanks for the information.
Frantic.

0

LVL 19

Expert Comment

ID: 9883214
@Frantic_Orange:  I feel that you should always split the points amongst everyone who contributed to your solution, even if you don't use their stuff 100%.  szachs's code should work just fine for you as well.

Dex*
0

Author Comment

ID: 9911997

I took note of the single T-SQL solution and accepted that as the answer to my question.  However, I ended up using the cursor as I will be using a similar approach elsewhere but where more complicated processing is required.

Frantic
0

LVL 19

Expert Comment

ID: 9913444
@Frantic_Orange:  Thanks for the question.  This one was fun, AND had a good discussion to go with it.  :)

Peace,
Dex*
0

Author Comment

ID: 9918986
@Dex: Those are the kind of questions I like most - it seems that the last few questions I've asked have had some interesting discussions behind them.  Doesn't make me feel like such an amateur as I do when I first post the question.

Season's Greets,
Frantic.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month12 days, 22 hours left to enroll