• javib (3/22/2013)


    I have this query to express a set of business rules.

    To get the information I need, I tried joining the table on itself but that brings back many more records than are actually in the table. The table only has about 1700 records in it but I'm getting these results:

    rep_id rep_name Single Practitioner

    00718264UBONNIE COLLINS 8836

    00729486SROBERT YACHINI 96

    01078482MSHABNAM ELYASZADEH 75

    00726313TSHELLY PRATHER 75

    01005121VSTACI HARTMAN 71

    00603023JNURIA SWABY 68

    Below is the query I've tried. What am I doing wrong?

    SELECT DISTINCT a.rep_id, a.rep_name, count(*) AS 'Single Practitioner'

    FROM SE_Violation_Detection a inner join SE_Violation_Detection b

    ON a.rep_id = b.rep_id and a.hcp_cid = b.hcp_cid

    group by a.rep_id, a.rep_name

    having count(*) >= 2

    ORDER BY count(*) DESC

    Based on the data displayed above, what are you expecting to be returned?