Write query efficiently

Question

Hi I am working out a query where it has to exclude records based on certain criteria. For instance in the following code I will have to exclude all the Customers who had only "No Show" for the Event and the venue is "USA".

For instance ID 5, 6, 10. Can do it in 1 query rather than use an "Union" with 2 different queries. Please see code below.

DECLARE @Event TABLE
(ID INT
 , [Event] VARCHAR(100)
 , venue VARCHAR(100))
INSERT INTO @Event
SELECT 1, 'No Show', 'USA'
UNION 
SELECT 1, 'Labor Day', 'USA'
UNION 
SELECT 2, 'No Show', 'USA'
UNION
SELECT 2, 'Halloween', 'USA'
UNION
SELECT 3, 'No Show', 'CANADA'
UNION
SELECT 3, 'New Year Eve', 'CANADA'
UNION
SELECT 3, 'xmas', 'CANADA'
UNION
SELECT 4, 'July 4th', 'USA'
UNION 
SELECT 4, 'No Show', 'USA'
UNION
SELECT 4, 'Presidents day', 'USA'
UNION
SELECT 5, 'No Show', 'USA'
UNION
SELECT 6, 'No Show', 'USA'
UNION
SELECT 7, 'No Show', 'CANADA'
UNION
SELECT 7, 'No Show', 'MEXICO'
UNION
SELECT 8, 'No Show', 'UK'
UNION
SELECT 9, 'No Show', 'CHINA'
UNION
SELECT 10, 'No Show', 'USA'
--SELECT *
--FROM @Event
; WITH cte AS
 (
SELECT ID, [Event], venue, DENSE_RANK() OVER(PARTITION BY ID ORDER BY CASE WHEN [Event] = 'No Show' THEN  2 ELSE 1 END, [Event]) AS Rnk 
FROM @Event 
WHERE venue = 'USA'
 )
SELECT ID, [Event], venue FROM cte 
WHERE ([Event] <> 'No Show' OR Rnk <> 1)
UNION ALL
SELECT ID, [Event], venue
FROM @Event
WHERE venue <> 'USA'

Thanks.............

Answer 1

Use GROUP BY and MAX and MIN to accomplish this -- somewhat similar to "relational division".  Perhaps something like this:

select
  id,
  venue
from @event
where venue = 'USA'
  and event <> 'No Show'
group by id, venue;
/* -------- Output: --------
id          venue
----------- ------
1           USA
2           USA
4           USA
*/

After seconds I thought the query above would cover it without the use of any aggregate functions.

Answer 2

; WITH cte AS
 (
SELECT ID, [Event], venue, Count(ID) OVER(PARTITION BY ID) AS Rnk 
FROM @Event 
 )
SELECT ID, [Event], venue FROM cte 
WHERE ((([Event] = 'No Show' AND Rnk > 1) or [Event] <> 'No Show')  
AND venue = 'USA' ) 
Or venue <> 'USA'
Order by ID

Answer 3

Try:

DECLARE @Event TABLE
(ID INT
 , [Event] VARCHAR(100)
 , venue VARCHAR(100))
INSERT INTO @Event
SELECT 1, 'No Show', 'USA'
UNION 
SELECT 1, 'Labor Day', 'USA'
UNION 
SELECT 2, 'No Show', 'USA'
UNION
SELECT 2, 'Halloween', 'USA'
UNION
SELECT 3, 'No Show', 'CANADA'
UNION
SELECT 3, 'New Year Eve', 'CANADA'
UNION
SELECT 3, 'xmas', 'CANADA'
UNION
SELECT 4, 'July 4th', 'USA'
UNION 
SELECT 4, 'No Show', 'USA'
UNION
SELECT 4, 'Presidents day', 'USA'
UNION
SELECT 5, 'No Show', 'USA'
UNION
SELECT 6, 'No Show', 'USA'
UNION
SELECT 7, 'No Show', 'CANADA'
UNION
SELECT 7, 'No Show', 'MEXICO'
UNION
SELECT 8, 'No Show', 'UK'
UNION
SELECT 9, 'No Show', 'CHINA'
UNION
SELECT 10, 'No Show', 'USA'



;WITH CTE
AS
(
 SELECT ID, [Event], Venue, DENSE_RANK() OVER (PARTITION BY ID ORDER BY [Event], Venue)Dr
 FROM @Event
),
CTE2
AS
(
  SELECT ID, [Event], Venue, MAX(Dr) OVER (PARTITION BY ID)MxDr
  FROM CTE
)

SELECT ID, Event, venue 
FROM CTE2
WHERE 1 = CASE WHEN MxDr = 1 AND Event = 'No Show' AND venue = 'USA' THEN 0 
			   ELSE 1 END
ORDER BY 1			   

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 4

Please try below.

select * from @Event
where ID not in (select ID from @event where Event = 'No Show' and venue = 'USA'
and ID not in (select ID from @Event where event <> 'No Show'))

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

Hi, Please execute below query simply:

select  id,  venue  from @event 
where venue = 'USA'  and event <> 'No Show'  
group by id, venue;

Ahsan Kabir
Please remember to click Mark as Answer and Vote as Helpful on posts that help you.
This can be beneficial to other community members reading the thread.
http://www.aktechforum.blogspot.com/

Answer 6

May be this will do :

select ID FROM @Event
WHERE Venue = 'USA'
Group BY ID  
HAVING MIN(Event) = MAX(Event)

---

Thanks and regards, Rishabh K