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 (AT) europe (DOT) com) writes:
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 (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...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 (AT) europe (DOT) com> wrote



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




Use the TOP 5 .
You will have to sum and then compare.



Beware of spamming the client.

"mountain man" <hobbit (AT) southern_seaweed (DOT) com.op> wrote

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
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 (AT) algonet (DOT) se> wrote

Sam (masystem (AT) europe (DOT) com) writes:
You did not answer which DB enging you are using. It does not seem
to be SQL Server:


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 (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp