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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
All Courses

From novice to tech pro — start learning today.