Need help with semi duplicate row elimination

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.

dd.customer_contacts
dd.contact_referral
dd.referral_category

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.

select		 distinct
			 cc.contact_date
			,cc.contact_type
			,cc.customers_seen
			,cc.made_referral
			,cc.customer_contacts_id
			,rc.[referral_category_name]
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
where	cc.made_referral='Y'

Open in new window


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.
dummy-data-objects.sql
ER-diagram.JPG
result.set.i.have.JPG
result.set.i.want.JPG
LVL 1
sqlagent007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
If I interpret your request correctly you don't have a problem with the results really, you just want to blank out some data when it is repeated. Is that correct?
blanks instead of repititionYou do realise that SQL isn't the right vehicle for doing that through I hope. SQL will repeat the values and it does this for good reasons, for example how would you re-order that information if there were blanks in it? (tip you can't).
0
PortletPaulfreelancerCommented:
How about this instead:
|      CONTACT_DATE | CONTACT_TYPE | CUSTOMERS_SEEN | MADE_REFERRAL | CUSTOMER_CONTACTS_ID |   REFERRAL_CATEGORIES |
|-------------------|--------------|----------------|---------------|----------------------|-----------------------|
| November, 01 2014 |    In Person |              3 |             Y |                    1 | Phone,In Person,eMail |
| November, 02 2014 |        Phone |              1 |             Y |                    3 |                 eMail |
| November, 04 2014 |    In Person |              2 |             Y |                    5 |                 Phone |

Open in new window

Produced by the following CROSS APPLY ...  STUFF ... FOR XML PATH
SELECT
      CC.contact_date
    , CC.contact_type
    , CC.customers_seen
    , CC.made_referral
    , CC.customer_contacts_id
    , CA.REFERRAL_CATEGORIES
FROM customer_contacts CC
      CROSS APPLY (
            SELECT
                  STUFF ((
                              SELECT
                              ',' + rc.[referral_category_name]
                              FROM contact_referral CR
                              LEFT JOIN referral_category RC ON CR.referral_category_id = RC.referral_category_id
                              WHERE CC.customer_contacts_id = CR.customer_contacts_id
                              FOR xml PATH ('')
                              )
            , 1, 1, '')
      ) CA (REFERRAL_CATEGORIES)
WHERE CC.made_referral = 'Y'
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
or maybe this:
|      CONTACT_DATE | CONTACT_TYPE | BY_PHONE | IN_PERSON | BY_EMAIL | CUSTOMERS_SEEN | MADE_REFERRAL | CUSTOMER_CONTACTS_ID |
|-------------------|--------------|----------|-----------|----------|----------------|---------------|----------------------|
| November, 01 2014 |    In Person |        1 |         1 |        1 |              3 |             Y |                    1 |
| November, 02 2014 |        Phone |        0 |         0 |        1 |              1 |             Y |                    3 |
| November, 04 2014 |    In Person |        1 |         0 |        0 |              2 |             Y |                    5 |

Open in new window

produced by
SELECT
      CC.contact_date
    , CC.contact_type
    , RC.BY_PHONE
    , RC.IN_PERSON
    , RC.BY_EMAIL
    , CC.customers_seen
    , CC.made_referral
    , CC.customer_contacts_id
FROM customer_contacts CC
      INNER JOIN (
            SELECT
                  CR.customer_contacts_id
                , COUNT(CASE WHEN referral_category_name = 'Phone'     THEN 1 END) AS BY_PHONE
                , COUNT(CASE WHEN referral_category_name = 'In Person' THEN 1 END) AS IN_PERSON
                , COUNT(CASE WHEN referral_category_name = 'eMail'     THEN 1 END) AS BY_EMAIL
            FROM contact_referral CR
                  LEFT JOIN referral_category RC ON CR.referral_category_id = RC.referral_category_id
            GROUP BY CR.customer_contacts_id
      ) RC ON CC.customer_contacts_id = RC.customer_contacts_id
;

Open in new window


ref: http://sqlfiddle.com/#!3/88e0e/12
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sqlagent007Author Commented:
Thanks @PortletPaul! The solution you provided worked great, however I am just wondering if you would expand on this comment: "You do realise that SQL isn't the right vehicle for doing that through I hope."

We have been leaning towards some kind of cube solution (SSAS) for a while, thinking that we should stop doing this ad-hoc data manipulations. Is this what you meant? Or is there some reporting solution that maybe we should look into. For the most part we use Crystal Reports.

Thanks for your help. I will accept your solution for the TSQL, it worked great.
0
sqlagent007Author Commented:
This is exactly what I was looking for thank you.
0
PortletPaulfreelancerCommented:
Hi. I simply meant that trying to suppress parts of a row (literally as it appears in my image) is not a good use of sql, and typically a presentation layer (eg html, BI such as crystal) would be a better method.

As both of my suggested dolutions avoid this pattial row issue that vomment no longer spplies.

A cube is often an excelllent way to provide BI dolutions but I wasnt thinking of that when I msde the comment.

Cheers, Paul
0
PortletPaulfreelancerCommented:
Apologies for spelling above. Phone keyboard and clumsy thumbs.
0
sqlagent007Author Commented:
Thanks @PortletPaul, I looked for a solution in Crystal first before posting. I could not find one. I actually like this solution a lot because if puts the multiple values on 1 line and saves the CSR from printing 50 pages to submit his / her report.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.