Need help with Count (Distinct) statement

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

This is what I have for a query:

SQL

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

The executed result has first column header MTMRN, with data values (for example)
637668
637668
664356
664356
664356
664356
664356
693426
693426
709799
709799
086693
086693

May I have assistance with a statement for a distinct count on this column. The answer here will be 5, as there are 5 distinct numbers. Mind you there are some data with leading zeros.

Much appreciated. Sikaman

Posted 10-Apr-13 6:43am

Sikaman

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2013-04-10T06:52:00

Solution 1

Try:

SQL

select distinct MTMRN from (place here the query you have)

Please note the parenthesis. This is called select from select type subquery. If MTMRN is a character typed field, leading zeros will present no issue, they are characters like the others.

Posted 10-Apr-13 6:52am

Zoltán Zörgő

Updated 10-Apr-13 6:54am

v2

Comments

Sikaman 10-Apr-13 13:14pm

Hi Zoltan, I tried the solution to no avail. This is what I did:

and select distinct MTMRN from (sh-sql01.DataWarehouse-SQLQuery.Exam_SchedProv_Age_v2.sql*)

and this is the message:

Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '-'.

José Amílcar Casimiro 10-Apr-13 13:22pm

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

)

Sikaman 10-Apr-13 17:37pm

Jose, thank you. It works. Much appreciated!

Zoltán Zörgő 10-Apr-13 15:29pm

Yes, José is right. What you tried is a nonsense. I suggested you to write a query around your current query and not around some file name. A file name is not a query, neither is the file itself. It might contain a query but t-sql can't use it in any form. You could however make a view from it. But that's an other thing.

Sikaman 10-Apr-13 17:40pm

Thank you, I have it corrected. Perfectly understood.