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/techin.../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.c om...

> 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_IdCust_ID(FK)Acct_NoTrans_type (FK)Trans_AmountTrans LimitTrans_date
10081001 12345100 208212 2062122002-02-03
10082001 12345100 17552062122002-02-03
10083001 12345100 307122062122002-02-03
10084001 12345100 106212 2062122002-02-03
10085001 12345100 100212 2062122002-02-03
10096001 12345100 116000 2062122002-01-03
10097001 12345100 110970 2062122002-01-03
10098002 10345100 116975 100502002-01-03
10099001 12345100 284412062122002-01-03
10000001 12345100 283912062122002-01-03
|10001003 12346100 1537 2062122001-12-03
10002003 12346100 1376 2062122001-12-03
10003002 10345100 16234 100502001-12-03
10104001 12345100 16374 2062122001-12-03
10105001 12345100 52875 2062122001-12-03
10106001 12345100 450 2062122001-12-03
10107001 12345100 7875 2062122001-12-03
10108002 10345100 16374 100502001-12-03

Transaction Type Table

TransType_ID (PK)T_Type
100Electronic Transaction
102DD

Customers

Customer_ID(PK)Account_No Cust_Name
0112345A. Jones
0210345K. Mark
0312346E. 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/techin.../2000/books.asp|||I think your previous message hit the nail on the head, this is a
homework assignment. Anybody in a position to do this is real life
would know how.

On Sun, 28 Dec 2003 23:40:24 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>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.