automate t-sql

UPDATE AGS_Hands_InProcess
SET Win = 5,-- the value from the matching Adjustement
ts = GETDATE() -- the timestamp of last action on this hand
WHERE WIn IS NULL -- unfinished games
AND ts_started < DATEADD(hh, -1, GETDATE()) -- older than 1 hour

tai985 (11/19/2012)

Sorry actually you are right. I forgot one important detail.

here is exactly what I do.

first I run this query:
select *
from AGS_Hands_InProcess nolock
where WIn is NULL -- unfinished games
and ts_started<dateadd(hh,-1,getdate()) -- older than 1 hour
Order by ts_started

result1:
handid playerHandle AGSGameID UserID NumberOfBets TotalBets Win PJWin ts_started ts

92007 298582538509785800218712590009111296 10253 C205092323 1 10.0000 .0000 2012-11-18 15:14:03.277 2012-11-18 15:14:03.277

then I run this 2nd query:
select top 100 * from UserBalanceAdjustments nolock
where ReasonForAdjustment like 'ADJ AGS%'
and transactiontime>dateadd(ww,-1,getdate()) -- for efficiency, limit the time window, e.g. a week
order by transactiontime desc

result2:
UserID TransactionTime Amount BalanceBeforeAdjustment ReasonForAdjustment OperatorID TransactionNumber CategoryID
C205092323 2012-11-18 16:25:00.497 10.0000 147.4000 ADJ AGSGameID=10253 handid=0 ph=0 txnid=184904 0 797706 18

Before I can run the update below. I need to make sure that the userid "C205092323" exist on the result of the second query and the ts_started in the first result "2012-11-18 16:25:00.497" is prior to the transaction time in the second result "2012-11-18 16:25:00.497". Sometimes the transaction doesn't exist in the second result, but it does exist on the first result which mean we don't need to do the update. Another scenario is there are two transaction in the first result for the same userid, but there is only one transaction in the second result. which means we need to compare the transaction time to make sure we update the correct transaction. Another issue is the AGSGameID is different each time, sometimes it is 10253, 10251, or 10252. So these are the reason why we cannot do it all at once.

UPDATE AGS_Hands_InProcess SET
Win= 5, -- the value from the matching Adjustement
ts= getdate() -- the timestamp of last action on this hand
WHERE HandID= 92007
and UserID= 'C205092323'
and AGSGameID= 10253
and Win is NULL -- protection.

Lowell (11/14/2012)

with what you've shown so far, you can certainly do them all at once; so you've left some important details out of the question at hand.

it's probably more a question of comfort and familiarity of the code, rather than "the boss says it cannot be done.

i guess you should show all the work you are doing...what are you doing between the copy / paste of the results, and the manual construction of a second command?

if nothing, then you've got the option to update all of them like i stated.

SELECT *
FROM AGS_Hands_InProcess
WHERE Win is NULL -- protection.
AND ts_started < DATEADD(hh, -1, GETDATE())
and cuserid IN ( select
cuserid
from UserBalanceAdjustments
where ReasonForAdjustment like 'ADJ AGS%'
and AGS_Hands_InProcess .transactiontime>dateadd(ww,-1,getdate()) -- for efficiency, limit the time window, e.g. a week
)

UPDATE AGS_Hands_InProcess SET
Win= 5, -- the value from the matching Adjustement
ts= getdate() -- the timestamp of last action on this hand
WHERE Win is NULL -- protection.
AND ts_started < DATEADD(hh, -1, GETDATE())
and cuserid IN ( select
cuserid
from UserBalanceAdjustments
where ReasonForAdjustment like 'ADJ AGS%'
and AGS_Hands_InProcess.transactiontime>dateadd(ww,-1,getdate()) -- for efficiency, limit the time window, e.g. a week
)