T-SQL query help

I have the following data, please see logic and I am looking for the result set.  Thank you,

USERID            User_Name      AccountTypeID      AccountTypeName
11111            John Doe      1001                  Checking
11111            John Doe      1002                  Saving

11112            Mary Doe      1002                  Saving

11113            John Smith      1001                  Checking

11114            Mary Smith      1001                  Checking
11114            Mary Smith      1003                  CD
11114            Mary Smith      1004                  Money Market

11115            John Lee      1002                  Saving
11115            John Lee      1003                  CD

11116            Mary Lee      1005                  IRA


Logic:
AccountTypeID = 1001, Letter send = Y
or
AccountTypeID = 1002, Letter send = Y
or
AccountTypeID = 1001 and AccountTypeID = 1002, Letter send = Y

Else Letter_send = N
      
Result:
USERID      User_Name       Letter_Sent      AccountTypeName
11111      John Doe              Y             Checking, Saving
11112      Mary Doe      Y             Saving
11113      John Smith      Y             Checking
11114      Mary Smith      N             Checking, CD, Money Market
11115      John Lee         N             Saving, CD
11116   Mary Lee    N             IRA
jfreeman2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Your description implies that if

  AccountTypeID = 1001 and AccountTypeID = 1002 and AccountTypeID = 105, Letter send = N

But I suspect that to not be what you want.

So

  SELECT DISTINCT userid, username FROM mytable where AccountTypeID in (1001, 1002)

Should suffice.


Good Luck,
Kent
Russ SuterSenior Software DeveloperCommented:
The letter sent one is easy. I'll have to look into how to combine multiple rows into a single comma separated column.

SELECT
[USERID],
[User_Name],
CASE WHEN (AccountTypeID IN (1001,1002) THEN 'Y' ELSE 'N' END AS [Letter_Sent],
[AccountTypeName]
FROM
TableA

Open in new window

jfreeman2010Author Commented:
Hi Russ,

AccountTypeID IN (1001, 1002), result UserID with 'Y' : 11111, 11112, 11113, 11114, 11115.  Total 5 recs .  The logic looking to return with 'Y': 11111, 11112, 11113.  UserID: 11114, 11115 should return with 'N'.

Thank you for helping...
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

jfreeman2010Author Commented:
Maybe logic should write like this:

If only have 1 account, and AccountTypeID in (1001, 1002), set letter_send to 'Y'
else if only have 2 account, and 1 accountTypeID= 1001, and 1accountTypeID = 1002, set letter_send to 'Y'
else set letter_send to 'N'.
Kent OlsenDBACommented:
  SELECT DISTINCT userid, username,
    case when (SELECT count(*) FROM mytable t1where t0.userid=t1.user and not AccountTypeID in (1001, 1002)) = 0
         then 'Y'
         else 'N'
    end
  FROM mytable t0 
  WHERE AccountTypeID in (1001, 1002)
    AND (SELECT count(*) FROM mytable t1where t0.userid=t1.user and not AccountTypeID in (1001, 1002) = 0;

Open in new window

That
Kent OlsenDBACommented:
Dang it.  You don't need the final filter.

SELECT DISTINCT userid, username,
    case when (SELECT count(*) FROM mytable t1where t0.userid=t1.user and not AccountTypeID in (1001, 1002)) = 0
         then 'Y'
         else 'N'
    end
  FROM mytable t0 
  WHERE AccountTypeID in (1001, 1002)

Open in new window

jfreeman2010Author Commented:
Hi Kdo,

Thank you very much for reply.  Very close, the return missing userid 11116:


create table #temp1(      userid int, username varchar(50), accountTypeid int, accountTypeName varchar(50));
    GO  
      insert into #temp1 values(11111, 'John Doe', 1001, 'Checking');
        insert into #temp1 values(11111, 'John Doe', 1002, 'Saving');
      insert into #temp1 values(11112, 'Mary Doe', 1002, 'Saving');
        insert into #temp1 values(11113, 'John Smith', 1001, 'Checking');
        insert into #temp1 values(11114, 'Mary Smith', 1001, 'Checking');
        insert into #temp1 values(11114, 'Mary Smith', 1003, 'CD');
        insert into #temp1 values(11114, 'Mary Smith', 1004, 'Money Market');
        insert into #temp1 values(11115, 'John Lee', 1002, 'Saving');
        insert into #temp1 values(11115, 'John Lee', 1003, 'CD');
        insert into #temp1 values(11116, 'Mary Lee', 1005, 'IRA');
    GO


select * from #temp1;

SELECT DISTINCT userid, username,
    case when (SELECT count(*) FROM #temp1 t1 where t0.userid=t1.userid and not AccountTypeID in (1001, 1002)) = 0
         then 'Y'
         else 'N'
    end
  FROM #temp1 t0
  WHERE AccountTypeID in (1001, 1002)

  drop table #temp1;

Thank you very much for helping...
Kent OlsenDBACommented:
Just drop the filter from the query.  Fat fingers on my part....
jfreeman2010Author Commented:
Which filter should drop???
Kent OlsenDBACommented:
The last line.  I've commented here.  When I first wrote the query I was going to manage the selection in the filter, but realized that it was more effective to do it inline and forgot to drop the last line.


SELECT DISTINCT userid, username,
    case when (SELECT count(*) FROM mytable t1where t0.userid=t1.user and not AccountTypeID in (1001, 1002)) = 0
         then 'Y'
         else 'N'
    end
  FROM mytable t0 
  --WHERE AccountTypeID in (1001, 1002)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
yes, that works.  Thank you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.