Update column based on value in previous row

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.
Frantic_OrangeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
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
Frantic_OrangeAuthor Commented:
@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
HilaireCommented:
Please post more information

Table structure, fields for the ordering, ...
Thks
Hilaire
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Frantic_OrangeAuthor Commented:
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
szacksCommented:
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
szacksCommented:
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
DexstarCommented:
@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
DexstarCommented:
@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
szacksCommented:
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
Frantic_OrangeAuthor Commented:
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
LowfatspreadCommented:
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
szacksCommented:
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
LowfatspreadCommented:
yes code it how it feels best to you...
then if performance isn't acceptable
examine ways of making it so...

0
DexstarCommented:
@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
LowfatspreadCommented:
thanks dex*
hadn't realized isnull   was just case when a is null then b else a end
not like coalesce at all....
0
DexstarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
szacksCommented:
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
Frantic_OrangeAuthor Commented:
@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
DexstarCommented:
@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
Frantic_OrangeAuthor Commented:
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
DexstarCommented:
@Frantic_Orange:  Thanks for the question.  This one was fun, AND had a good discussion to go with it.  :)

Peace,
Dex*
0
Frantic_OrangeAuthor Commented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.