Solved

Update column based on value in previous row

Posted on 2003-12-04
22
729 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now