Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Use CASE for multiple occurrences

Posted on 2015-01-27
Last Modified: 2015-02-02
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. Code Result
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
                       WHEN 6 THEN attending_6 END AS attending, dealer_trade_expo.address, dealer_trade_expo.address2, dealer_trade_expo.city, 
                      dealer_trade_expo.state, dealer_trade_expo.zip_code, dealer_trade_expo.phone, dealer_trade_expo.fax, dealer_trade_expo.email,dealer_trade_expo.salesrep, 
                      ISNULL(dealer_trade_expo.attending_day1,'N') AS Monday, ISNULL(dealer_trade_expo.attending_day2,'N') AS Tuesday,dealer_trade_expo.registrant_type,
					  (CASE WHEN  dealer_trade_expo.dinner_1 = 'Y' OR dealer_trade_expo.dinner_2 = 'Y' OR  dealer_trade_expo.dinner_3 = 'Y'OR  dealer_trade_expo.dinner_4 = 'Y'OR dealer_trade_expo.dinner_5 = 'Y'OR  dealer_trade_expo.dinner_6 = 'Y' THEN 'YES' ELSE 'NO' END) AS DINNER
FROM         dealer_trade_expo CROSS JOIN
                          (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')
ORDER BY dealer_trade_expo.businessname, attending#s.attending#

Open in new window

Table View
Question by:skull52
LVL 24

Accepted Solution

chaau earned 500 total points
ID: 40573965
You need to reformat the CASE for the dinner column similar to you attending column, i.e. use the attending# pseudocolumn:
   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 

Open in new window


Author Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question