Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

automate t-sql Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 1:33 PM
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?
Post #1383873
Posted Monday, November 12, 2012 2:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 12,905, Visits: 32,176
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1383883
Posted Wednesday, November 14, 2012 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 1:33 PM
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


Post #1384853
Posted Wednesday, November 14, 2012 2:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 12,905, Visits: 32,176
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1384857
Posted Thursday, November 15, 2012 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 6:30 PM
Points: 191, Visits: 900
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
Post #1385146
Posted Friday, November 16, 2012 3:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 5,189, Visits: 12,049
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1385572
Posted Monday, November 19, 2012 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 1:33 PM
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.
Post #1386494
Posted Monday, November 19, 2012 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 1:33 PM
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.
Post #1386507
Posted Monday, November 19, 2012 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 12,905, Visits: 32,176
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1386538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse