Finding anomalies in data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding anomalies in data

Author

Topic

Bunks
Starting Member

10 Posts

Posted - 2009-11-24 : 17:02:58

Hi there

I am running a script that returns pension payments for members at a given date (see script and results below).

With regard to the last column ("TotalPaid"), is there a script I can write to just pull out anamolies in this field e.g. is $583.33 each month then $401.52 in the last column? I would just want to see the $401.52.

SELECT
q1.id,
q1.MemberReference,
q1.Surname,
q1.Initials,
q1.Status,
q1.SchemeCode,
q1.NINO,
q1.DOB,
qsit.EffectiveDate,
qsit.TotalPaid

from query001suba q1

join (Select Id, EffectiveDate, sum(TransAmount) as TotalPaid
from querysubInvestmentTransaction
where TransType = 'Reg Pension Pay'
group by id, EffectiveDate) qsit on qsit.id = q1.id

order by q1.Id, qsit.EffectiveDate

RESULTS:

AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2007-07-30 00:00:00.000 583.33
AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2007-08-28 00:00:00.000 583.33
AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2007-09-28 00:00:00.000 583.33
AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2007-10-29 00:00:00.000 583.33
AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2007-11-28 00:00:00.000 583.33
AP00xxxxx AP00xxxxx Banks K ACTIVE xxxxxxxx NULL xxxx-01-12 00:00:00.000 2009-08-31 08:52:04.000 401.52
Thanks!

Luke

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-25 : 03:46:29

try a variation of...

select b.*
from mytable a
inner join mytable b on a.id = b.id and b.date = a.date + 1
where b.oddvalue <> a.oddvalue

Bunks
Starting Member

10 Posts

Posted - 2009-11-25 : 22:59:10

Thanks Andrew - the only issue is that 583.33 won't always be the consistenmt amount (may be 583.32 or 583.34). Also, I have about 1,500 other members with different amounts there too.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-26 : 06:28:01

You hadn't defined "anomoly" so I put in one version. Below is another. Once you define anomoly to cover all cases you are interested in, then code can be created to work with that set.

select b.*
from mytable a
inner join mytable b on a.id = b.id and b.date = a.date + 1
where b.oddvalue <> a.oddvalue +- tolerancevalue

or produce an average/median value for each employee in mytable, and see if the last one (using max date) is different.

or find the last and 2nd last records (going by max-date) per employee and comparing the amounts.

maybe alter the underlying database, to report differences as they occur, via triggers rather than try to infer/workout the differences by re-examining the data after the fact.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources