Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


automate t-sql


automate t-sql

Author
Message
tai985
tai985
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 66
I'm not an expert at T-SQL, so I hope someone can help me automate this.

Currently, I'm doing this manually by run the select statement below

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

Result:
handid   playerHandle   AGSGameID   UserID   NumberOfBets   TotalBets   Win   PJWin   ts_started   ts
73483   2985823242791533679003040941990496   10253   C202933641   1   10.0000      .0000   2012-11-08 20:10:26.523   2012-11-08 20:10:26.523

Then I copy the handid, userid, AGSGameID above into the update query below and execute.

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= 73483
   and UserID= 'C202933641'
   and AGSGameID= 10253
   and Win is NULL -- protection.

How do I automate the part where I copy the handid, userid, AGSGameID above into the update query?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14931 Visits: 38919
ok, let me twist the question around on you...
why can't you update all the rows at the same time, where WIN is null, and ts_started is less than an hour, regardless of which userID?


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




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tai985
tai985
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 66
Thanks. I confirmed with my boss and he said it cannot be done as you suggest.

So any other suggestion to the original question?

Lowell (11/12/2012)
ok, let me twist the question around on you...
why can't you update all the rows at the same time, where WIN is null, and ts_started is less than an hour, regardless of which userID?


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



Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14931 Visits: 38919
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.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
I know it is still early in the day, but given the logic of the select and update, would this not work?

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
--note: sort doesn't matter here
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8308 Visits: 19449
Thanks. I confirmed with my boss and he said it cannot be done as you suggest.

So any other suggestion to the original question?


Can you please elaborate on the reasons why not. Lowell's solution is fast and seems logically equivalent to what you are doing.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
tai985
tai985
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 66
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.

tai985
tai985
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 66
For example:

In the first result below, there are three transactions with userid C205167665, C205170740, CU05166287 and there is no matching transactions in the second result. so we cannot update these three transaction.

for the last transaction in the first result set (userid C205007890). there is a transaction in the second result set with the same userid. However, the time in the first result is older than the time in the second result, so we cannot update this either.

handid   playerHandle   AGSGameID   UserID   NumberOfBets   TotalBets   Win   PJWin   ts_started   ts
85846   298582840218230346162491548775104485   10253   C205167665   1   300.0000      .0000   2012-11-15 01:36:07.733   2012-11-15 01:36:07.717
86032   298582159824523077755772467070104522   10251   C205170740   1   5.0000      .0000   2012-11-15 01:36:19.093   2012-11-15 01:36:19.077
85897   298582459650415492445714527713104617   10251   CU05166287   1   1.0000      .0000   2012-11-15 03:22:29.860   2012-11-15 03:22:29.860
85898   298582418496737235513024155330104618   10253   C205007890   1   5.0000      .0000   2012-11-15 03:23:03.907   2012-11-15 03:23:03.907

(4 row(s) affected)

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
C205170370   2012-11-17 12:25:00.773   3.0000   10.5100   ADJ AGSGameID=10253 handid=0 ph=0 txnid=181873   0   797582   18
C804750004   2012-11-16 19:25:00.493   200.0000   .0800   ADJ AGSGameID=10251 handid=0 ph=0 txnid=179018   0   797485   18
C205037875   2012-11-16 10:25:01.367   53.0000   913.9400   ADJ AGSGameID=10251 handid=0 ph=0 txnid=177712   0   797403   18
CU05170632   2012-11-15 14:25:00.913   8.0000   317.0000   ADJ AGSGameID=10251 handid=0 ph=0 txnid=175586   0   797166   18
C205007890   2012-11-15 02:25:00.217   2.0000   431.7500   ADJ AGSGameID=10253 handid=0 ph=0 txnid=173235   0   797006   18
CU05171189   2012-11-14 18:25:00.863   5.0000   521.0000   ADJ AGSGameID=10251 handid=0 ph=0 txnid=172258   0   796996   18
CU05148337   2012-11-13 18:25:02.010   1.0000   63.0000   ADJ AGSGameID=10251 handid=0 ph=0 txnid=167437   0   796913   18
CU05148337   2012-11-13 18:25:01.933   1.0000   62.0000   ADJ AGSGameID=10251 handid=0 ph=0 txnid=167436   0   796912   18
CU05169278   2012-11-13 14:24:59.803   150.0000   1096.0000   ADJ AGSGameID=10251 handid=0 ph=0 txnid=165988   0   796902   18

(10 row(s) affected)



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.

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14931 Visits: 38919
looks like no problem...you simply need to add that additional check to your update...

something like this looks correct to me, base don what you posted so far:
this would be the query that should show you everything in the current matching criteria i think you've provided:


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
)




and this would be the all inclusive update:


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
)



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search