I have a SQL 2005 standard edition database that has a unique application mod that is causing me a bit of a reporting headache. The 3 tables involved are attached to this post with the data insert statement as well.
The main table that counts interactions with customers and potential new customers is dd.customer_contacts. This table has 1 row for every customer interaction (phone, email, in person). A new feature to the application is to track if the CSR has received a referral during the initial customer contact. The CST can receive many different referral types during 1 customer interaction. When a CSR receives more than 1 referral in a single customer interaction, the report that counts the initial customer contacts gets inflated.
Here is an example of my issue.... the query below needs to return a total [customers_seen] = 3 for customer_contacts_id=1, however it will return a value of 9 (3 rows with 3 customers seen) because there are 3 referrals made and the way the relationship works, this query will return 3 rows every way I have tried to manipulate the JOIN. The customer_contacts_id exists 3 times in the contact_referral table and will therefore return 3 rows causing the customers_seen count of 3 to show 9. I need to eliminate 6 of the 9 customers seen on that day.
from dd.customer_contacts cc
left join dd.contact_referral cr on cc.customer_contacts_id=cr.customer_contacts_id
left join dd.referral_category rc on cr.referral_category_id=rc.referral_category_id
I need to figure out how to multiple referral_category_names without showing more than 1 row for each customer_contacts_id.
I have attached 2 images: 1) What I a getting, 2) What I wish I could get.