Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    9

    Unanswered: How do you write this query?

    I'm having trouble with the following problem. could someone give me a hint on how to solve?

    I need to write a stored procedure or statement that detects consecutive bank transactions measured against a threshold.

    For each customer (customers have many accounts), generate an alert or put the data into an exception table for those transactions, the sum of any consecutive set of 5 transactions that exceed the threshold (so amount could be 1,1,1,1,1 which is > 4: Move these transactions into an exception table)

    If the customer has 6 transactions then this would be considered two sets...

    How do you write this in sql?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    trans is the transaction table and trans_exp is the exception table which has the same structure

    [dba01][/dba01]$db2 describe table trans

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- -----
    WHEN_CREATED SYSIBM TIMESTAMP 10 0 Yes
    TRANSID SYSIBM INTEGER 4 0 Yes
    AMT SYSIBM INTEGER 4 0 Yes

    3 record(s) selected.

    [dba01][/dba01]$cat tsql
    insert into trans_exp
    with temp as
    -- SORT the transactions in order of transaction time
    (
    select transid,when_created,amt,rownumber() over (order by when_created) as row#
    from
    trans
    ),
    temp1 (atrans,bwhen,btrans,bamt,arow,brow)
    as
    -- Create Transaction sets
    (
    select a.transid,b.when_created,b.transid,b.amt,a.row#,b. row#
    from
    temp a,
    temp b
    where b.row# between a.row# and a.row#+4
    )
    ,
    temp2(a2trans,b2sum)
    as
    -- Identify the transaction set exceeding the threshold. A set is considered only if the set has 5 transactions
    (
    select atrans,sum(bamt) from temp1 group by atrans having (count(*)=5 and sum(bamt)>100)
    )
    -- List the elements of the transaction set which exceed the threshold and insert into the exception table
    select bwhen,btrans,bamt from temp1 where atrans in (select a2trans from temp2)
    ;


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •