Variation on a prior ? - COUNT

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

General SQL Server Forums

New to SQL Server Programming

Variation on a prior ? - COUNT

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-16 : 09:21:41

Client has a table in which every row with the same Arrestno should have the same ECSOID value. If there are multiple Arrestnos and they have different ECSOID's, the data is fubar.

So, in a previous post, i need to find the instances in a table where the arrestno value occurred more than once. The following worked for that:


select *
from [ARR_AFSS]
where ARRESTNO in
(
select ARRESTNO
from ARR_AFSS
group by ARRESTNO
having count(*) > 1
)

(thank you pduffin).

However, now i need to do the "same" thing, but what i need to find are instances where the arrestno repeats, but the corresponding ECSOID values are different in those groups.


ARRESTNO	ecsoid
GCSO00ARR001038	GCSO00MNI001038
GCSO00ARR001038	GCSO00MNI000971
GCSO00ARR001038	GCSO00MNI000971
GCSO00ARR001041	GCSO00MNI000971
GCSO00ARR001041	GCSO00MNI000971
GCSO00ARR001181	GCSO00MNI001088
GCSO00ARR001181	GCSO00MNI001088
GCSO00ARR001202	GCSO00MNI001108

So in the case of this sample data, where you see arrestno ending in 1038, you should only see one corresponding ecsoid. in this case there is an outlyer (ending in 1038 for the ecsoid). So my select should find all such instances. Good data is identified by like arrestnos matching with all alike ecsoid's, as in the arrestno ending in 1041 and the ecsoid ending in 971. These should NOT show up in my select.

How do i modify the above to capture that?

thanks

james

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-16 : 09:57:51

select *
from [ARR_AFSS] as a
where exists(select * from [ARR_AFSS] as b where b.ARRESTNO = a.ARRESTNO and b.ecsoid <> a.ecsoid)

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-16 : 10:00:27

Or a variation of your existing query:

select *
from [ARR_AFSS]
where ARRESTNO in
(
select ARRESTNO
from ARR_AFSS
group by ARRESTNO
having count(DISTINCT ecsoid) > 1
)

But, then it is not clear how you would identify which one of those is "fubar" (to use your terminology). What if there were two rows for a given arrestno, each with its own ecsoid?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-16 : 11:19:57

Fred. Thanks thanks thanks.

This brought me much closer to seeing what is really wrong with the clients data. As i ran this and began sorting through the entries, what i found was that the specific entries i need to deal with are where the LAST SIX digits of the ECSOID are the same as the last six digits of the ARRESTNO. So what i really need this query to do is find the instances where Substring(ECSOID,9,15) = Substring(ARRESTNO,9,15). So would that look like this?:


select *
from [ARR_AFSS] as a
where exists(select * from [ARR_AFSS] as b where b.substring(ARRESTNO,9,15) = a.substring(ECSOID,9,15)

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-16 : 11:39:55

Sunita: The way the data "needs" to look, is this:

Every arrestno can only be associated with ONE ECSOID. I can have four arrestno's and they must all have the same ECSOID associated.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-16 : 12:03:10

i just tried this as a test:



select ARRESTNO, ECSOID
from ALLWARRANTSERRORCHECK a
where exists(select ARRESTNO, ECSOID from ALLWARRANTSERRORCHECK as b where Substring(b.arrestno,10,15) = Substring(a.ECSOID,10,15))

but that is pulling back


ARRESTNO	ECSOID
GCSO00ARR053136	GCSO00MNI031196
GCSO00ARR053136	GCSO00MNI031196
GCSO00ARR031111	GCSO00MNI019995
GCSO00ARR012605	GCSO00MNI009332
GCSO00ARR013023	GCSO00MNI009332
GCSO00ARR013333	GCSO00MNI009332
GCSO00ARR031007	GCSO00MNI019946
GCSO00ARR017357	GCSO00MNI012047
GCSO00ARR052020	GCSO00MNI022329
GCSO00ARR032149	GCSO00MNI020567
GCSO00ARR041004	GCSO00MNI025495
GCSO00ARR015992	GCSO00MNI009826
GCSO00ARR018728	GCSO00MNI009826
GCSO00ARR029175	GCSO00MNI018962

Clearly these are not matching left to right and the resultset totals 2373 rows. Whereas running the following:


select ARRESTNO, ECSOID
from ALLWARRANTSERRORCHECK a
where exists(select ARRESTNO, ECSOID from ALLWARRANTSERRORCHECK as b where b.arrestno = a.arrestno and b.ecsoid <> a.ecsoid) order by arrestno

is producing the following:



ARRESTNO	ECSOID
GCSO00ARR001038	GCSO00MNI001038
GCSO00ARR001038	GCSO00MNI000971
GCSO00ARR001038	GCSO00MNI000971
GCSO00ARR002808	GCSO00MNI012401
GCSO00ARR002808	GCSO00MNI002376
GCSO00ARR002808	GCSO00MNI002376
GCSO00ARR003239	GCSO00MNI002718
GCSO00ARR003239	GCSO00MNI003239
GCSO00ARR003717	GCSO00MNI003717
GCSO00ARR003717	GCSO00MNI022671

with 349 total results in the resultset.

i'm confused.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources