Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Use CASE for multiple occurrences

Posted on 2015-01-27
2
Medium Priority
?
78 Views
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
0
Comment
Question by:skull52
[X]
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
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 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:
(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

Open in new window

0
 

Author Comment

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 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