Post reply

Query help

  • August 11, 2009 at 12:04 am

    #209113

    I have a table that holding financial transactions. Every transaction will have TranDate, Auth_type, SeqNo, Amt. If it is approved, auth type will be 1. For some reasons, after approval, the transaction may get rejected. The rejected transaction will have another entry with the same SeqNO with Auth_type = 2. Sometimes, the rejection might happen after few days. So, the transaction will have different TranDate without approval code for the day. the schema of the table

    create table #DailyTran (

    TranDate datetime not null,

    Auth_type smallint not null,

    SeqNo varchar(10) not null,

    Amt1 decimal(12,3)

    )

    insert #DailyTran values ('2009-08-11 08:47:01.947', 1, '0000001',45.00)

    insert #DailyTran values ('2009-08-11 08:47:50.540', 2, '0000001',45.00)

    insert #DailyTran values ('2009-08-11 08:48:40.090', 1, '000045545',234.00)

    insert #DailyTran values ('2009-08-11 08:49:58.840', 2, '0343401',60.00)

    insert #DailyTran values ('2009-08-11 08:51:09.947', 1, '0867766001',18.00)

    I want to get the only rejected transaction without the parent entry (auth_type =1). I just ignore the approved transaction and approved/rejected transaction with the same seqno.

    In this example, i would like get the 4th row (auth_type = 2, seqno '0343401', amt =60.00). Please help me with this query.

  • August 11, 2009 at 12:41 am

    #1037244

    Hi,

    try this

    select seqNo,Auth_type,Amt1

    from #DailyTran

    where Auth_type = 2

    and seqNo not in (select seqNo

    from #DailyTran

    where Auth_type = 1)

  • August 11, 2009 at 1:09 am

    #1037256

    Thanks arun.

  • August 12, 2009 at 12:17 am

    #1037855

    Dear All,

    Here, i need one more query. i want to delete nullified transactions. meaning, all rows with same (seqno and auth_type 1 and 2). So that, i will have only approved transaction.

    please help me with this query.

Viewing 4 posts - 1 through 3 (of 3 total)

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