skull52
asked on
Use CASE for multiple occurrences
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
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#
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER