[SQL Server] Counting duplicates |
| Posted: 18 May 2013 11:44 AM PDT newbie questionI 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 SQLSELECT p.first_name"Provider", COUNT(distinct demo.last_name, demo.first_name) TotalCountFROM 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) anddx.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(*) > 1ORDER BY p.provider_no;gets me optimistic numbers as a result setProvider TotalCountPeter 111Tom 156Mark 124Phillip 45Femi 12However when I manually check Femi I get 9 where there are multiple BP entries (the correct answer) and 3 where there are only one readingWhat am I doing wrong? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment