Counting duplicates

Question

Post reply

Counting duplicates

phc

Grasshopper

Points: 22
More actions
May 18, 2013 at 6:44 pm

#394800

newbie question
I am trying to count the number of diabetic patients who have had their blood pressure checked twice or more at the clinic in the last year but I am lost in a muddle of a SQL
SELECT p.first_name"Provider", COUNT(distinct demo.last_name, demo.first_name) TotalCount
FROM measurements m, demographic demo, dxresearch dx, provider p
WHERE m.type="BP" and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and
dx.dxresearch_code = '250' and
demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and
demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and
demographicNo =demo.demographic_no and dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no
group by p.first_name
HAVING COUNT(*) > 1
ORDER BY p.provider_no;
gets me optimistic numbers as a result set
ProviderTotalCount
Peter111
Tom156
Mark124
Phillip45
Femi12
However when I manually check Femi I get 9 where there are multiple BP entries (the correct answer) and 3 where there are only one reading
What am I doing wrong?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

phc Grasshopper Points: 22 More actions · Answer 1

I have gotten a bit further on by nesting a query, but I am not sure I have the distinct right

SELECT C.first_name"Provider", Count(C.provider_no) "Diabetics with over 1 BP in 12 months"

FROM

(Select distinct CONCAT(demo.last_name, ',', demo.first_name) as name, p.provider_no, p.first_name, Count(*)

FROM measurements m, demographic demo, dxresearch dx, provider p

WHERE m.type='BP' and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and

dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and

demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and m.demographicNo =demo.demographic_no and

dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by name

HAVING Count(*) > 1 ) as C

GROUP BY C.provider_no

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

this looks like MySQL code ??

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

subhajeetsur SSC-Addicted Points: 453 More actions · Answer 3

is the query conditions listed correctly . It may happen some conditions are not applied correctly because of spaces or something in the source table column values

OTF SSCertifiable Points: 6104 More actions · Answer 4

phc (5/18/2013)
I have gotten a bit further on by nesting a query, but I am not sure I have the distinct right
SELECT C.first_name"Provider", Count(C.provider_no) "Diabetics with over 1 BP in 12 months"
FROM
(Select distinct CONCAT(demo.last_name, ',', demo.first_name) as name, p.provider_no, p.first_name, Count(*)
FROM measurements m, demographic demo, dxresearch dx, provider p
WHERE m.type='BP' and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and
dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and
demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and m.demographicNo =demo.demographic_no and
dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by name
HAVING Count(*) > 1 ) as C
GROUP BY C.provider_no

"CONCAT"? I don't believe this is TSQL (well, certainly not SQL 2008).

You will be better served by posting in Forums for the dialect of SQL you are using.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

OTF (5/22/2013)
phc (5/18/2013)
I have gotten a bit further on by nesting a query, but I am not sure I have the distinct right
SELECT C.first_name"Provider", Count(C.provider_no) "Diabetics with over 1 BP in 12 months"
FROM
(Select distinct CONCAT(demo.last_name, ',', demo.first_name) as name, p.provider_no, p.first_name, Count(*)
FROM measurements m, demographic demo, dxresearch dx, provider p
WHERE m.type='BP' and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and
dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and
demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and m.demographicNo =demo.demographic_no and
dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by name
HAVING Count(*) > 1 ) as C
GROUP BY C.provider_no
"CONCAT"? I don't believe this is TSQL (well, certainly not SQL 2008).
You will be better served by posting in Forums for the dialect of SQL you are using.

FYI: CONCAT is a T-SQL SQL 2012 feature.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Alan.B (5/28/2013)
OTF (5/22/2013)
phc (5/18/2013)
I have gotten a bit further on by nesting a query, but I am not sure I have the distinct right
SELECT C.first_name"Provider", Count(C.provider_no) "Diabetics with over 1 BP in 12 months"
FROM
(Select distinct CONCAT(demo.last_name, ',', demo.first_name) as name, p.provider_no, p.first_name, Count(*)
FROM measurements m, demographic demo, dxresearch dx, provider p
WHERE m.type='BP' and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and
dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and
demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and m.demographicNo =demo.demographic_no and
dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by name
HAVING Count(*) > 1 ) as C
GROUP BY C.provider_no
"CONCAT"? I don't believe this is TSQL (well, certainly not SQL 2008).
You will be better served by posting in Forums for the dialect of SQL you are using.
FYI: CONCAT is a T-SQL SQL 2012 feature.

Cool! However, EXTRACT and CURDATE do not appear to be new functions in SQL Server 2012.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

Lynn Pettis (5/28/2013)
Alan.B (5/28/2013)
OTF (5/22/2013)
phc (5/18/2013)
I have gotten a bit further on by nesting a query, but I am not sure I have the distinct right
SELECT C.first_name"Provider", Count(C.provider_no) "Diabetics with over 1 BP in 12 months"
FROM
(Select distinct CONCAT(demo.last_name, ',', demo.first_name) as name, p.provider_no, p.first_name, Count(*)
FROM measurements m, demographic demo, dxresearch dx, provider p
WHERE m.type='BP' and m.dateObserved > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-12)*100)+1) and
dx.dxresearch_code = '250' and demo.patient_status = 'AC' and demo.roster_status not in ('TE-terminated','transient') and
demo.provider_no in ('101','102','105','120','121') and dx.status = 'A' and m.demographicNo =demo.demographic_no and
dx.demographic_no = demo.demographic_no and p.provider_no = demo.provider_no group by name
HAVING Count(*) > 1 ) as C
GROUP BY C.provider_no
"CONCAT"? I don't believe this is TSQL (well, certainly not SQL 2008).
You will be better served by posting in Forums for the dialect of SQL you a re using.
FYI: CONCAT is a T-SQL SQL 2012 feature.
Cool! However, EXTRACT and CURDATE do not appear to be new functions in SQL Server 2012.

I was replying to "'CONCAT'? I don't believe this is TSQL (well, certainly not SQL 2008)." Purely FYI :hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001