Solved

Update column based on value in previous row

Posted on 2003-12-04
22
734 Views
Last Modified: 2012-10-01
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
Comment
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
  • Learn & ask questions
  • 6
  • 6
  • 5
  • +2
22 Comments
 
LVL 26

Expert Comment

by:Hilaire
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

by:Frantic_Orange
ID: 9875211
@Hilaire: Thanks for the reply.

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

by:Hilaire
ID: 9875273
Please post more information

Table structure, fields for the ordering, ...
Thks
Hilaire
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:Frantic_Orange
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

by:szacks
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

by:szacks
szacks earned 50 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

by:Dexstar
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

by:Dexstar
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

by:szacks
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

by:Frantic_Orange
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

by:Lowfatspread
Lowfatspread earned 25 total points
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

by:szacks
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

by:Lowfatspread
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

by:Dexstar
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

by:Lowfatspread
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

by:
Dexstar earned 50 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

by:szacks
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

by:Frantic_Orange
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

by:Dexstar
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

by:Frantic_Orange
ID: 9911997
Thanks for the suggested answers.,

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

by:Dexstar
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

by:Frantic_Orange
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question