I a have a large list of suppliers and a larger list supplier email addresses

Question

I a have a large list of suppliers and a larger list sender email addresses. Sometimes a supplier will have the same sender email address called 'processor x'as the sole sender emailing, another supplier might have many sender email addresses but includes 'processor x' as one of the senders. and another supplier might have many sender email addresses, but does not have 'proccessor x' as one of their emails.

I want to flag suppliers with only the sender email address of 'processor x' as a rank of 1, but other suppliers with the other 2 conditions mentioned as 2.

How can I do this in RANK? Is this possible ?

MAny thanks for your time.

Would be useful if you could mock out a sample of your two tables with one or two rows representative as how the table is structured. I would have thought though you could simply left join on 'processor x' and then a simple case statement to check if the join returns a null — el_oso, Apr 16 '20 at 14:06

score 1 · Answer 1 · answered Apr 16 '20 at 15:48

I would approach this by using a selective count operator. Something like this:

Declare @Temp 
Table   (
           Customer VarChar(100),
           SendingOrganization varchar(100),
           Sender_Email_Address varchar(100)
        );

Insert Into @Temp Values('entweiler','a1','processor x');
Insert Into @Temp Values('entweiler','a2','processor x');
Insert Into @Temp Values('entweiler','a3','processor x');
Insert Into @Temp Values('entweiler','a4','x@y.com');
Insert Into @Temp Values('entweiler','a4','processor x');
Insert Into @Temp Values('entweiler','a4','processor x');
Insert Into @Temp Values('entweiler','a4','processor x');
Insert Into @Temp Values('entweiler','a4','processor x');
Insert Into @Temp Values('entweiler','a5','a@b.com');
Insert Into @Temp Values('entweiler','a5','b@c.com');
Insert Into @Temp Values('entweiler','a5','c@d.com');
Insert Into @Temp Values('entweiler','a5','d@e.com');
Insert Into @Temp Values('entweiler','a5','e@f.comx');
Insert Into @Temp Values('entweiler','a5','processor x');
Insert Into @Temp Values('entweiler','a6','f@g.com');

With Emails As
(
    Select  SendingOrganization,
            Count(Case when sender_email_address = 'processor x' Then 1 End) As WithProcessor,
            Count(Case when sender_email_address <> 'processor x' Then 1 End) As WithoutProcessor,
            Count(*) As AllEmail
    From    @Temp
    Group By SendingOrganization
)
Select  T.*,
        Case When WithProcessor = AllEmail Then 1
             When WithoutProcessor = allEmail Then 3
             Else 2
             End As Rank
From    Emails
        Inner Join @Temp T
            On Emails.SendingOrganization = T.SendingOrganization

I a have a large list of suppliers and a larger list supplier email addresses

1 Answers1