sql update query based on pre-dates

[mpc123]

I have a table of names that have done something on a certain date, now this could be over and over again yet of course on different dates but never the same. I need to update a field called last_time that corresponds to the previous date that they occur and update the last_time with a sum of 2 other fields,

Quick example Table

id , date , name , score, holes, last_time

1, 2015-01-01, him 1, 80, 18, (NULL)
2, 2015-01-01, him 2, 79, 18, (NULL)
3, 2015-01-01, him 3, 45, 18, (NULL)
4, 2015-01-02, him 1, 79, 9, (NULL)
5, 2015-01-02, him 2, 50, 9, (NULL)
6, 2015-01-02, him 3, 20, 9, (NULL)
7, 2015-01-03, him 1, 50, 18, (NULL)
8, 2015-01-03, him 2, 40, 18, (NULL)
9, 2015-01-03, him 3, 90, 18, (NULL)

Of course singularly and on the same id would be

update exampleTable set last_time = (score / holes) where name ="him 1"

But I need from the previous dates info.

For example I would need to update last_time on id 1 with id 4 score / holes, then id would be need last_time updating with id 7 score / holes. This table has about 100000 rows in and each date could have differening number of people taking part of course all with different names, with this I need to get the info for each person of course if they occur more than once.

I hope someone can help wit this, Im reallly struggling.

[mpc123]

Sorry the bottom part should read

For example I would need to update last_time on id 1 with id 4 score / holes, then id4 would be need last_time updating with id 7 score / holes and so on and so on. This table has about 100000 rows in and each date could have differening number of people taking part of course all with different names, with this I need to get the info for each person of course if they occur more than once.

I hope someone can help wit this, Im reallly struggling.

Can anyone help?

[mpc123]

first time ever I haven't had a reply, anybody?

[WebspoonUK]

Hi mpc123,

I think I understand what you're getting at. I would take care of this at the point you insert each data record...

Just before you execute your INSERT statement, check for the presence of their last results as follows: -

DECLARE @LastId int, @LastScore int, @LastHoles int
SET @LastId = 0

SELECT TOP 1 @LastId = [Id], @LastScore = Score, @LastHoles = Holes
FROM ExampleTable ET with (nolock)
WHERE Name = 'him 1'
ORDER BY [date] desc

This will grab the latest values for that person based on their name.

Then, perform your INSERT statement (presuming you have identity insert on for [Id]): -

DECLARE @LastScoreUpdate int
SET @LastScoreUpdate = null
IF @LastId > 0 and @LastHoles > 0 --don't allow divide by zero
  SET @LastScoreUpdate = @LastScore / @LastHoles

insert into exampleTable([date] , name , score, holes, last_time)
values(getdate(), 'him 1', @score, @holes, @LastScoreUpdate)

Wrap all this up in a stored procedure for ultimate efficiency.

NB. It is never a good idea to do a varchar comparison in your WHERE clause. Try and make use of an account or something so that you can compare on an id (integer) as this will perform much faster (especially if it is a FK to PK on another table).

If you want to update existing data, you're best off using a one off CURSOR in conjunction with the above solution. Give me a shout if you need help with writing cursors.

Hope this helps.