Duplicate and Out of Sequence Numbers

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

Duplicate and Out of Sequence Numbers

Author

Topic

seema4
Starting Member

1 Post

Posted - 2014-04-07 : 22:19:26

Each patient should have unique randomID and these randomID's should be in sequence order, So I should bring back (Duplicate RandomID) and (Out of Sequence RandomID)

here is my query

declare @valueCheck table
(RowNumber Int Identity
,PatientID varchar(25)
,RandomID varchar(25))

insert into @ValueCheck
(PatientID, RandomID)

select
PatientID
, RandomID

From dbo.CurrentData

where QuestionID= 200
order by patientid, RandomID

if I apply this condition, I'll get (out of sequence RandomID)
Select s1.PatientID, s1. RandomID as OutOFSequence
From @ValueCheck s1
Left Join @ValueCheck s2
On s1.RandomID = s2.RandomID – 1
Where s2.RandomID is Null

and if I apply this , I'll get (Duplicate RandomID)
select * from @ValueCheck
where RandomID in
(Select RandomID
From @ValueCheck
Group by RandomID
Having Count(RandomID) > 1)
what I want is, I want a table that brings both condition together

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-04-08 : 07:42:26

Maybe something like:


WITH OutOfs
AS
(
	SELECT PatientID, RandomID AS OutOFSequence
	FROM @valueCheck C1
	WHERE NOT EXISTS
	(
		SELECT 1
		FROM @valueCheck C2
		WHERE C1.RandomID = C2.RandomID -1
	)
)
,Dups
AS
(
	SELECT PatientID, RandomID AS DupRandom
	FROM @valueCheck C1
	WHERE EXISTS
	(
		SELECT 1
		FROM @valueCheck C2
		WHERE C1.RandomID = C2.RandomID
		GROUP BY C2.RandomID
		HAVING COUNT(*) > 1
	)
)
SELECT COALESCE(O.PatientID, D.PatientID) AS PatientID
	,O.OutOFSequence, D.DupRandom
FROM OutOfs O
	FULL JOIN Dups D
		ON O.PatientID = D.PatientID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources