Grouping Issue for Emails

Question

Grouping Issue for Emails

Paul Stasny

Mr or Mrs. 500

Points: 599
More actions
February 21, 2021 at 7:14 am
Go to Answer
#3847956
Greetings,
I am trying to organize contact emails based on very specific criteria and would appreciate some guidance. This info is coming from 3 tables and my requirements are:
1. Columns: ContactId, Default email, Default Email type, Second Email, Second Email Type, Third Email, Third Email Type
2. If a contact has 1 email, it populates the Default email. If a contact has 2 emails, it populates the Default & Secondary
3. If a Contact has 3 emails, it populates the default. If a "Work " type email exists, it goes into Secondary. If an "Alternate" type exists it goes into the Third Email Field. If the type doesn't exist, put any email into second and third.
4. If a contact has more than 3 emails, the defaul email goes into default, If a "Work" type exists it goes into secondary and if an alternate type exists, it goes into the third email. If the type doesn't exist, pick any of the non defaul emails to populate second and third
The tables joins are simple but I'm not sure how to approach the grouping. A particular contact may have as many as 10 emails although I only want 3 of them
My Tables are structure as follows
Contact Emailindex Email
Contactid, fname, lname ContactId, Emailid, Default, Type EmailId, Email Address
1 John Doe 1 5 1 Home 5 john@gmail.com
2 Pete Smith 2 6 1 Work 6 pete@gmail.com
1 John Doe 1 7 0 Work 7 john@ibm.com
1 John Doe 1 8 0 Alternate 8 john@yahoo.com
1 John Doe 1 9 0 Hobby 9 john@verizon.com
- This topic was modified 3 years, 9 months ago by Paul Stasny.
Phil Parkin

SSC Guru

Points: 246960
More actions
February 21, 2021 at 11:03 am
Answer
#3848023
1. You have stated your requirements, but not asked any questions. What do you want assistance with?
2. If you are hoping for coding assistance, please provide sample DDL, along with sample data in the form of INSERT statements and desired results based on your sample data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul Stasny Mr or Mrs. 500 Points: 599 More actions · Answer 1

I'm sorry I wasn't clear on what I was trying to do. Essentially, I was having issues trying to Group the data in the three tables shown so I could pull out as many as 3 emails/Client based on the requirements stated. I have come up with a workable solution several subqueries and ranking functions. Basically, I broke this up into 8 Groups and ran ranking functions on each Group based in the internal values of the Email Type fields. The values I wanted to key on had the largest values so it worked well with ranking functions. It isn't the most elegant solution but I can package it into a SP and it seems to produce the desired results.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Your solution sounds more complicated than it needs to be, but well done on working out something usable.

May I suggest that in future you do as I suggested and provide sample DDL, data and desired results. There are lots of talented people here and maybe you'd have obtained a better solution than what you have now.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin