please see below the data and the Schema DDL:
Accounts Table
Trans_Id Cust_ID(FK) Acct_No Trans_type (FK) Trans_Amount Trans Limit Trans_date
10081 001 12345 100 208212 206212 2002-02-03
10082 001 12345 100 1755 206212 2002-02-03
10083 001 12345 100 30712 206212 2002-02-03
10084 001 12345 100 106212 206212 2002-02-03
10085 001 12345 100 100212 206212 2002-02-03
10096 001 12345 100 116000 206212 2002-01-03
10097 001 12345 100 110970 206212 2002-01-03
10098 002 10345 100 116975 10050 2002-01-03
10099 001 12345 100 28441 206212 2002-01-03
10000 001 12345 100 28391 206212 2002-01-03
|10001 003 12346 100 1537 206212 2001-12-03
10002 003 12346 100 1376 206212 2001-12-03
10003 002 10345 100 16234 10050 2001-12-03
10104 001 12345 100 16374 206212 2001-12-03
10105 001 12345 100 52875 206212 2001-12-03
10106 001 12345 100 450 206212 2001-12-03
10107 001 12345 100 7875 206212 2001-12-03
10108 002 10345 100 16374 10050 2001-12-03
Transaction Type Table
TransType_ID (PK) T_Type
100 Electronic Transaction
102 DD
Customers
Customer_ID(PK) Account_No Cust_Name
01 12345 A. Jones
02 10345 K. Mark
03 12346 E. Gander
Create table Accounts ( Trans_Id Number Primary Key
Trans_Amount Number,
Trans_Date Date,
Trans_limit Number,
Acct_No Number,
Trans_type References Transaction_Type,
Cust_Id References Customers);
Create table Customers (Cust_Id Number Primary Key
Acct_No Number,
Cust_Name Varchar 2);
Create table Transaction_Type (TransType_Id Number Primary Key
T_Type Varchar 2);
Thanks,
Sam.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...
[posted and mailed]
Sam (ma******@europe.com) writes: I want to monitor any suspicious financial transaction which take
place in a bank through electronic transfer.
There are three tables Customers, Account and transaction_type.
How can I write a SQl to report the following:
Detect an outbound Electronic transfer that is unusually high,
compared to a set threshold.
For each customer, generate alerts if any outbound Electronic Transfer
exceeds threshold.
Detect Electronic Transfer that are high, compared to a set threshold.
For each customer, generate alerts if any set of last 5 outbound
Electronic Transfers exceeds the set threshold.
Detect Electronic Transfer that are high, compared to historical
behavior of the customer.
For each customer, generate alerts if any set of last 5 Electronic
Transfer (the average of all sets of 5 outbound Electronic Transfer +2
standard deviation points)
First: decide which engine you want this implemented on. You have
cross-posted this to three newsgroups for three different products,
and the solution is not likely to be the same for all three. Unless,
portability is a requirement, but then you need to specify this.
Second, do you really expect anyone to write a query for you, without
the tables at hand. There is a standard recommendation for these kind
of questions, and that is to include:
o CREATE TABLE statements for your table(s).
o INSERT statements with sample data.
o The desired output from that sample data.
That makes it possible for anyone to post a tested solution.
Unless... this is really a school assignment, in which case you should
solve the problem yourself and not ask someone else to do it.
