Skip to main content
N
New_York_Guy
Known Participant
November 7, 2008
Question

Query help

  • November 7, 2008
  • 2 replies
  • 275 views
I need to query a transaction table to get the last transaction for each ticket number. Normally, I would just do somthing like select max(txn_id) as current_txn where ticket_no = '#form.ticket_not#' (txn_id will link to the master table to get the actual txn name,ie sales, return, etc.)

Howver, they changed the requirments in obtaining the last transcation.
The last transaction is 1 if max(txn_id) = 1
else
The last transaction is 2 if max(txn_id) = 2 or (max(txn_id) = 4 and max(code) = 1)
else
The last transaction is 3 if max(txn_id) = 3 or (max(txn_id) = 4 and max(code) = 1)
else
The last transaction si 4 if max(txn_id) = 4 and max(code)=0
else
The last transactions is 5 if the txn_id = 5 and code=0 (no max used on this one)
else
The last transaction is 7 if txn_id = 7 and code = 0 (no max used on this one)
else
The last transaction is 6 if (txn_id = 6 and (txn_id <> 5 and code=0) or (txn_id<>7 and code=0))

How do I write the query based on the above requirements, to get the last transaction for a ticket number ?
    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    November 7, 2008
    Do you timestamp your transactions?
    N
    Newsgroup_User
    Inspiring
    November 7, 2008
    you did not say which db you are using, but most proper dbms have
    built-in control flow functions like IF / CASE WHEN THEN ELSE END. use a
    combination of those to set your last trx id.

    E.g:

    ... hmm, wanted to write an example for you, but there are seem to be a
    couple of holes in your business requirements... check this out:

    1)
    quote:

    The last transaction is 2 if max(txn_id) = 2 or (max(txn_id) = 4 and
    max(code) = 1)
    else
    The last transaction is 3 if max(txn_id) = 3 or (max(txn_id) = 4 and
    max(code) = 1)


    so... if max(txn_id)=4 and max(code)=1, which last transaction do you
    need to assign - 2 or 3???

    2)
    quote:

    The last transactions is 5 if the txn_id = 5 and code=0 (no max used on
    this one)
    ...
    The last transaction is 6 if (txn_id = 6 and (txn_id <> 5 and code=0) or
    (txn_id<>7 and code=0))


    so... if txn_id=5 AND code=0, which last txn id do you need to assign?
    your first statement above makes it 5, but the next statement makes it 6
    because of txn_id<>7 and code=0
    (and i am assuming some of those txn_id=x in the last statement should
    actually be max(txn_id)? 'cos if txn_id=6 then it sure is not 5 and not
    7....)

    maybe make sure first you fully and correctly understand the business
    logic behind this...

    hth



    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    N
    New_York_GuyAuthor
    Known Participant
    November 7, 2008
    I am using sql server, and I can see how it is confusing to understand.

    Txn id 1, 2,3, 4 always following in that order, so the last one found, based on the criteria, should be considered the last transaction.But 4 can show up again later on.

    so The last transaction is 2 if max(txn_id) = 2 or (max(txn_id) = 4 and
    max(code) = 1)
    else
    The last transaction is 3 if max(txn_id) = 3 or (max(txn_id) = 4 and
    max(code) = 1)

    should be 3, since 3 is after 2. if there is no 3, then it should be 2.

    Txn id 5, 6, 7 can be in any order after 4

    so The last transactions is 5 if the txn_id = 5 and code=0 (no max used on
    this one) should be 5 and
    ...
    The last transaction is 6 if (txn_id = 6 and (txn_id <> 5 and code=0) or
    (txn_id<>7 and code=0)) should be 6 only if there is not a 5 or not a 7,otherwise it would be 5 or 7 (only one wll show up, not both).

    I can see how this is complicated and I have a hard time trying to understand it also, but if I can just get started in the right direction and build the if/case statements based on the requirements, I can see what it looks like.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices