Counting duplicates

  • 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?

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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

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

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