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?