Reporting Suspicious Financial Transactions

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)

[posted and mailed] Sam (masystem@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. -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

G'Day Sam, Without you posting the schedma for the data how can you expect the SQL to be applicable to your data? Below are some guesses ... "Sam" <masystem@europe.com> wrote in message news:51c326d2.0312271114.3e5f39fe@posting.google.com... > 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. Select 'EFT Alert for ' + Customers.customer_name + ' -- Date: ' + cast(account.transaction_date as varchar) + ' $Amt: ' + account.transaction_amount from account join customers in some manner where transaction_amount > XXX ---- insert threashold here > 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. Use the TOP 5 . You will have to sum and then compare. > 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) Beware of spamming the client.

"mountain man" <hobbit@southern_seaweed.com.op> wrote in message news:<yStHb.67523$aT.49791@news-server.bigpond.net.au>... > > Beware of spamming the client. Not much chance of you going into too much trouble doing that.. Hahahahahahahahaha

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 <sommar@algonet.se> wrote in message news:<Xns945F88C2497BYazorman@127.0.0.1>... > [posted and mailed] > > Sam (masystem@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.

Sam (masystem@europe.com) writes: > please see below the data and the Schema DDL: You did not answer which DB enging you are using. It does not seem to be SQL Server: > 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); There are no pre-defined data types Date or Number in SQL Server. And I don't think you can skip the data type altogether just because you have a REFERENCES constraint. Furthermore, the sample data was not in form of INSERT statements. Yes, getting correct scripts means that you will have to do some work, but since you are the one who is in need of a solution, that seems to me like a fair deal. -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp