SolvedPrivate

Need help with semi duplicate row elimination

Posted on 2014-11-10
8
27 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now