SolvedPrivate

Need help with semi duplicate row elimination

Posted on 2014-11-10
8
32 Views
Last Modified: 2016-02-18
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
0
Comment
Question by:sqlagent007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40434279
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40434297
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40434322
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:sqlagent007
ID: 40434984
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
 
LVL 1

Author Closing Comment

by:sqlagent007
ID: 40434985
This is exactly what I was looking for thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40436238
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40436242
Apologies for spelling above. Phone keyboard and clumsy thumbs.
0
 
LVL 1

Author Comment

by:sqlagent007
ID: 40436248
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Updating a field based from a if exist.... 2 31
GeoClustering  and AOG 25 41
Specify timing interval fro change data 2 52
What Is an Error? 2 21
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question