Solved

# Use CASE for multiple occurrences

Posted on 2015-01-27
53 Views
I have a table that has 6 columns to define if a show attendee will attend a dinner (up to 6).  For example if 3 people attend the show but only 2 of the 3 attendees will be attending the dinner i want to display a column called DINNER with the 2 attending as yes and the 3rd as no. I tried it with as case statement but the way I have it coded it displays the column correctly but the result is yes for all 3 including the one that is not attending.
``````SELECT     UPPER(dealer_trade_expo.accountnumber) AS accountnumber, UPPER(dealer_trade_expo.businessname)AS businessname ,CASE attending# WHEN 1 THEN attending_1 WHEN 2 THEN attending_2 WHEN 3 THEN attending_3 WHEN 4 THEN attending_4 WHEN 5 THEN attending_5
(SELECT     1 AS attending#
UNION ALL
SELECT     2 AS Expr1
UNION ALL
SELECT     3 AS Expr1
UNION ALL
SELECT     4 AS Expr1
UNION ALL
SELECT     5 AS Expr1
UNION ALL
SELECT     6 AS Expr1) AS attending#s
WHERE     (NOT (CASE attending# WHEN 1 THEN attending_1 WHEN 2 THEN attending_2 WHEN 3 THEN attending_3 WHEN 4 THEN attending_4 WHEN 5 THEN attending_5
WHEN 6 THEN attending_6 END = ' ')) AND dealer_trade_expo.accountnumber = 'A01913'--AND (dealer_trade_expo.special_event1 = 'Yes')
``````
0
Question by:skull52

LVL 24

Accepted Solution

chaau earned 500 total points
You need to reformat the CASE for the dinner column similar to you attending column, i.e. use the attending# pseudocolumn:
``````(CASE WHEN
dealer_trade_expo.dinner_1 = 'Y' AND attending# = 1 OR
dealer_trade_expo.dinner_2 = 'Y' AND attending# = 2 OR
dealer_trade_expo.dinner_3 = 'Y' AND attending# = 3 OR
dealer_trade_expo.dinner_4 = 'Y' AND attending# = 4 OR
dealer_trade_expo.dinner_5 = 'Y' AND attending# = 5 OR
dealer_trade_expo.dinner_6 = 'Y' AND attending# = 6
THEN 'YES' ELSE 'NO' END) AS DINNER
``````
0

Author Comment

chaau, your solution worked perfectly, I was close just missed that last part, Thanks.
0

## Featured Post

### Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…