Update int field with Datediff in subquery?

Question

Post reply

Update int field with Datediff in subquery?

dji

Ten Centuries

Points: 1229
More actions
March 26, 2009 at 1:02 am

#129431

A proper test for the SQL gurus who I am desperately trying to emulate:-)
I have two tables;
MstHorse
HorseID (PK unique)
HorseName
MstRun
RunID (PK unique)
RunDate
RunHorseID
DaysSinceLastRun
(both tables have other fields, omitted for clarity)
The relationship is one to many, one horse has multiple entries in MstRun. The records are added by an import routine that calculates the DaysSinceLastRun when adding a new row to the MstRun file, unfortunately there has been a bug which means many records contain the wrong number! When no previous entry exists value = 9999
Is it possible without me writing a vb app to fix the values using SQL?
I am thinking of;
use MstHorse as the master file to read all rows for HorseID in the MstRun file in chronological order, updating DaysSinceRun with the number of days since the previous entry. Results for eg;
RunID RunDate RunHorseID DaysSinceLastRun
1 01/01/2009 1 9999
....
.....
200 10/01/2009 1 9
etc
Help greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

HI,

try this code

create table MstRun

(

RunID int IDENTITY(1,1),

RunDate datetime,

RunHorseID int,

DaysSinceLastRun int

)

create procedure HorseID_Insert

(

@RunDate datetime,

@RunHorseID int

)

as

begin

declare @Days int, @DaysUP int

select @Days = 9999

if not exists (select 'X' from MstRun

where RunHorseID = @RunHorseID)

begin

insert into MstRun values (@RunDate,@RunHorseID,@Days)

end

else

begin

select @DaysUP = DaysSinceLastRun+1 from MstRun

where RunHorseID = @RunHorseID order by RunID

insert into MstRun values (@RunDate,@RunHorseID,@DaysUP)

end

ARUN SAS

dji Ten Centuries Points: 1229 More actions · Answer 2

Many thanks for the reply. Your code gave me the inspiration I needed to fix the data.

I resorted to creating a vb app as there was actually more bad data fields than I originally unearthed.

thanks again

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Here's a simple way of doing it:-- set up some sample data

DROP TABLE #MstHorse

CREATE TABLE #MstHorse (HorseID INT, HorseName VARCHAR(30))

INSERT INTO #MstHorse (HorseID, HorseName)

SELECT 1, 'Red Rum' UNION ALL

SELECT 2, 'Desert Orchid' UNION ALL

SELECT 3, 'Jo Brand'

DROP TABLE #MstRun

CREATE TABLE #MstRun (RunID INT, RunDate DATETIME, RunHorseID INT, DaysSinceLastRun INT)

INSERT INTO #MstRun (RunID, RunDate, RunHorseID, DaysSinceLastRun)

SELECT 1, GETDATE() - 40, 1, NULL UNION ALL

SELECT 2, GETDATE() - 30, 1, NULL UNION ALL

SELECT 3, GETDATE() - 20, 1, NULL UNION ALL

SELECT 4, GETDATE() - 10, 1, NULL UNION ALL

SELECT 5, GETDATE() - 15, 2, NULL UNION ALL

SELECT 6, GETDATE() - 10, 2, NULL UNION ALL

SELECT 7, GETDATE() - 6, 2, NULL UNION ALL

SELECT 8, GETDATE() - 5, 2, NULL

/*SELECT h.*, r.*

FROM #MstHorse h

LEFT JOIN #MstRun r ON r.RunHorseID = h.HorseID*/

-- This SELECT shows that the correlated subquery works

-- It can be easily turned into an UPDATE

SELECT r.*, DATEDIFF(dd, (SELECT MAX(RunDate) AS LastRun

FROM #MstRun

WHERE RunHorseID = r.RunHorseID

AND RunDate < r.RunDate), r.RunDate)

FROM #MstRun r

If this runs too slowly, you might want to look at the "running totals" type of solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dji Ten Centuries Points: 1229 More actions · Answer 4

Blimey! That looks impressive.

I have copied the code and will have a play around as I really do need to get on top of T-SQL instead of writing apps all the time.

thanks for the reply, much appreciated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

dji (3/26/2009)
Blimey! That looks impressive.
I have copied the code and will have a play around as I really do need to get on top of T-SQL instead of writing apps all the time.
thanks for the reply, much appreciated.

You're welcome, thanks for the feedback. Here's a link to an excellent article describing the running totals method:

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6