Grouping Issue for Emails

  • 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, 8 months ago by  Paul Stasny.
    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

  • 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.

  • 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

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

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