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 2005 Forums

Transact-SQL (2005)

Finding anomalies in data

Author

Topic

Bunks
Starting Member

10 Posts

Posted - 2009-11-23 : 23:07:29

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

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 02:00:02

can you post the table structure??
So that, it will be easy for me to try my level best and post some relevant query which meets your requirements...

Balaji.K

Sachin.Nand

2937 Posts

Posted - 2009-11-24 : 05:51:23

SELECT * FROM
(

SELECT *,
COUNT OVER(PARTITION BY TotalPaid)AS rowid FROM

(
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
) T

)T1 WHERE rowid=1

PBUH

Bunks
Starting Member

10 Posts

Posted - 2009-11-24 : 16:14:22

Thanks Idera, however I get the errors below when I run the script with your additions:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'over'.
Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near 'T'.

kbhere - which table structure do you need?

Thanks a lot!

Luke

Bunks
Starting Member

10 Posts

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

I was just told by someone here at work that, although we are on sql2005, we still have to use sql2000 for certain functions. I'll post in that forum also. I guess this will make a difference?

Thanks

Luke

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-24 : 17:39:44

quote:
Originally posted by Bunks

I was just told by someone here at work that, although we are on sql2005, we still have to use sql2000 for certain functions. I'll post in that forum also. I guess this will make a difference?

Yes it makes a difference. The solution provided by Idera is only available in compatibility levels 90 or 100. If you are on SQL Server 2005 and still need to use SQL Server 2000, then my assumption is that your compatibility level is set to 80.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Bunks
Starting Member

10 Posts

Posted - 2009-11-24 : 18:50:28

Cheers Tara!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-25 : 03:51:39

FYI...

posting a message twice is considered poor form. Please read the FAQ's.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136308

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources