Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
This is what I have for a query:
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 7:43am
Sikaman293

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try:
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.
  Permalink  
v2
Comments
Sikaman at 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 Ferreira Casimiro at 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 at 10-Apr-13 17:37pm
   
Jose, thank you. It works. Much appreciated!
Zoltán Zörgő at 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 at 10-Apr-13 17:40pm
   
Thank you, I have it corrected. Perfectly understood.

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

  Print Answers RSS
0 Kornfeld Eliyahu Peter 265
1 Sergey Alexandrovich Kryukov 202
2 George Jonsson 185
3 King Fisher 185
4 CPallini 155
0 OriginalGriff 6,223
1 DamithSL 4,724
2 Maciej Los 4,196
3 Kornfeld Eliyahu Peter 3,894
4 Sergey Alexandrovich Kryukov 3,496


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 10 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100