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?

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