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.
| 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. |
|
|
|
|
|
|
Finding anomalies in data