Need help with Count (Distinct) statement

Zoltan are telling you a something different:

select distinct MTMRN from (

select a.MTMRN, a.PatName, a.IDX6, a.ServiceDate, a.ExamName, a.Exam,
b.SchedProv, b.SchProvNPI, b.ApptDt, b.ApptDt2, b.ApptTm, b.ApptTm2,
b.VisitType, b.STATUS,
b.PCPNPI, b.Referring, b.RefNPI, b.Deceased,
c.source, c.payer, c.patient, c.dob, c.gender, c.addr1, c.addr2, c.city,
c.state, c.zip, c.phone, c.mbrid, c.pcp,
c.PastProv, c.PastAppt, c.cd, c.FutProv, c.FutAppt
from [DataWarehouse].[dbo].[BHMammColPats2] a
INNER JOIN [dbo].formatIDX_PastAppts b
on SUBSTRING(a.MTMRN, PATINDEX('%[^0 ]%', a.MTMRN + ' '), LEN(a.MTMRN)) =
SUBSTRING(b.MRN , PATINDEX('%[^0 ]%', b.MRN + ' '), LEN(b.MRN))
INNER JOIN [dbo].[Full_roster] c
on SUBSTRING(b.MRN, PATINDEX('%[^0 ]%', b.MRN + ' '), LEN(b.MRN)) =
SUBSTRING(c.MRN , PATINDEX('%[^0 ]%', c.MRN + ' '), LEN(c.MRN))
where a.Exam='COL'
and b.SchedProv='WEINSTEIN M.D.,ROBERT E.'
and b.STATUS='ARR'
and (b.VisitType='OVT' OR b.VisitType='AWV' OR b.VisitType='SWV')
and CAST(b.ApptDt as datetime) > = '2012-01-01 00:00:00.000'
and CAST(b.ApptDt as datetime) < = '2012-12-31 00:00:00.000'
and datediff(year,dob,getdate()) between 50 and 75
and datediff(year,ServiceDate,getdate()) < = 5

)

Jose, thank you. It works. Much appreciated!

Thank you, I have it corrected. Perfectly understood.