SolvedPrivate

Need help with semi duplicate row elimination

Posted on 2014-11-10
8
34 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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