SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting duplicates


Counting duplicates

Author
Message
phc
phc
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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
Provider TotalCount
Peter 111
Tom 156
Mark 124
Phillip 45
Femi 12

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?
phc
phc
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12612 Visits: 37674
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
subhajeetsur
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 27
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
OTF
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1312 Visits: 4128
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.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13906 Visits: 8015
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98534 Visits: 38996
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13906 Visits: 8015
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

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search