We have our team entering a dummy domain in some situations in our Email field, to signify that the entry belongs to a login ID list from another business unit (which uses a "gamertag" OR email as it's login ID). Something like this:

Retail Reps table a:

Users Table (provided from other business unit) b

We want to count the number of matches. Here's the CASE statement I wrote:

CASE WHEN REPLACE(a.email, '@dummydomain.com','') = b.userid THEN 1 ELSE 0 END AS [Email LoginID match]

John results in 0 (no match) and Jeff results in 1 (match), even though both are a match.

Any guidance greatly appreciated.

CodePudding user response：

This seems to work:

SELECT u.UserID, count(*) as [Email LoginID match]
FROM Users u 
INNER JOIN RetailReps rr on u.UserID = Replace(rr.email,'@dummydomain.com','')
GROUP BY u.UserID

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1569a6474024e04052ddcb4c4b14d23b

fwiw, you should probably be using a variant of example.com or dummydomain.test as your dummy domain.

CodePudding user response：

Well, I cannot see how you do the join between the tables, by this works for me:

create table e (email varchar(255))
insert into e values ('[email protected]')
insert into e values ('[email protected]')

create table u (user_id varchar(255))
insert into u values ('john123')
insert into u values ('[email protected]')
GO

4 rows affected

select 
*,
iif(user_id is null, 0, 1) as [Email LoginID match]
from e left join u
on left(e.email, charindex('@', e.email) - 1) = u.user_id
GO

email                   | user_id | Email LoginID match
:---------------------- | :------ | ------------------:
[email protected] | john123 |                   1
[email protected] | null    |                   0

db<>fiddle here