Thanks so much SQlraider for your reply. One of my friends actual helped me on this and it was something simple I was just having a brain fart. Here is the query that worked. I now know we only had 197 duplicate visits out of 20,000 so we can breathe a little sigh of relief now.

With MedicalCount_CTE

AS

(

Select

u.PatientID,

u.DateOfServiceFrom,

u.EncounterType

From

vwUDSTMP5 u

WHere

u.EncounterType = 'HCPC-Medical' and

u.BillableProviderName Not Like '%RN' AND

u.DateOfServiceFrom >'01/01/13'

),

BHCount_CTE

AS

(

Select

u.PatientID,

u.DateOfServiceFrom,

u.EncounterType

From

vwUDSTMP5 u

WHere

u.EncounterType = 'HCPC-Behavior Health' and

u.BillableProviderName Not Like '%RN' AND

u.DateOfServiceFrom >'01/01/13'

)

Select

c.PatientID,

c.DateOfServiceFrom

From

MedicalCount_CTE c INNER JOIN

BHCount_CTE bh ON bh.PatientID = c.PatientID and bh.DateOfServiceFrom = c.DateOfServiceFrom

order by

c.PatientID,

c.DateOfServiceFrom,

c.EncounterType