automate t-sql

  • 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:

    handidplayerHandleAGSGameIDUserIDNumberOfBetsTotalBetsWinPJWints_startedts

    73483298582324279153367900304094199049610253C202933641110.0000.00002012-11-08 20:10:26.5232012-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?

  • 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!

  • 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

  • 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!

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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:

    handidplayerHandleAGSGameIDUserIDNumberOfBetsTotalBetsWinPJWints_startedts

    9200729858253850978580021871259000911129610253C205092323110.0000.00002012-11-18 15:14:03.2772012-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:

    UserIDTransactionTimeAmountBalanceBeforeAdjustmentReasonForAdjustmentOperatorIDTransactionNumberCategoryID

    C2050923232012-11-18 16:25:00.49710.0000147.4000ADJ AGSGameID=10253 handid=0 ph=0 txnid=184904079770618

    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.

  • 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.

    handidplayerHandleAGSGameIDUserIDNumberOfBetsTotalBetsWinPJWints_startedts

    8584629858284021823034616249154877510448510253C2051676651300.0000.00002012-11-15 01:36:07.7332012-11-15 01:36:07.717

    8603229858215982452307775577246707010452210251C20517074015.0000.00002012-11-15 01:36:19.0932012-11-15 01:36:19.077

    8589729858245965041549244571452771310461710251CU0516628711.0000.00002012-11-15 03:22:29.8602012-11-15 03:22:29.860

    8589829858241849673723551302415533010461810253C20500789015.0000.00002012-11-15 03:23:03.9072012-11-15 03:23:03.907

    (4 row(s) affected)

    UserIDTransactionTimeAmountBalanceBeforeAdjustmentReasonForAdjustmentOperatorIDTransactionNumberCategoryID

    C2050923232012-11-18 16:25:00.49710.0000147.4000ADJ AGSGameID=10253 handid=0 ph=0 txnid=184904079770618

    C2051703702012-11-17 12:25:00.7733.000010.5100ADJ AGSGameID=10253 handid=0 ph=0 txnid=181873079758218

    C8047500042012-11-16 19:25:00.493200.0000.0800ADJ AGSGameID=10251 handid=0 ph=0 txnid=179018079748518

    C2050378752012-11-16 10:25:01.36753.0000913.9400ADJ AGSGameID=10251 handid=0 ph=0 txnid=177712079740318

    CU051706322012-11-15 14:25:00.9138.0000317.0000ADJ AGSGameID=10251 handid=0 ph=0 txnid=175586079716618

    C2050078902012-11-15 02:25:00.2172.0000431.7500ADJ AGSGameID=10253 handid=0 ph=0 txnid=173235079700618

    CU051711892012-11-14 18:25:00.8635.0000521.0000ADJ AGSGameID=10251 handid=0 ph=0 txnid=172258079699618

    CU051483372012-11-13 18:25:02.0101.000063.0000ADJ AGSGameID=10251 handid=0 ph=0 txnid=167437079691318

    CU051483372012-11-13 18:25:01.9331.000062.0000ADJ AGSGameID=10251 handid=0 ph=0 txnid=167436079691218

    CU051692782012-11-13 14:24:59.803150.00001096.0000ADJ AGSGameID=10251 handid=0 ph=0 txnid=165988079690218

    (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:

    handidplayerHandleAGSGameIDUserIDNumberOfBetsTotalBetsWinPJWints_startedts

    9200729858253850978580021871259000911129610253C205092323110.0000.00002012-11-18 15:14:03.2772012-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:

    UserIDTransactionTimeAmountBalanceBeforeAdjustmentReasonForAdjustmentOperatorIDTransactionNumberCategoryID

    C2050923232012-11-18 16:25:00.49710.0000147.4000ADJ AGSGameID=10253 handid=0 ph=0 txnid=184904079770618

    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.

  • 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!

Viewing 9 posts - 1 through 8 (of 8 total)

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