Maybe something like:WITH OutOfsAS( SELECT PatientID, RandomID AS OutOFSequence FROM @valueCheck C1 WHERE NOT EXISTS ( SELECT 1 FROM @valueCheck C2 WHERE C1.RandomID = C2.RandomID -1 )),DupsAS( 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.DupRandomFROM OutOfs O FULL JOIN Dups D ON O.PatientID = D.PatientID;