CODEWITHSUNDEEP
sqlgoogle-bigquery
kalyan4uonly
kalyan4uonly

Reputation: 335

How to pick records based on specific condition

I have a table named as "ticket_lc" look like below

enter image description here

From the above table i need to pick only the records which satisfy the condition

condition: the ticket status should be in "assigned" and "closed" and "resolved"

so in the above table only 102 ticket is satisfying the condition, if the ticket contains other than these 3 then my query should not pick those tickets.

can anybody help me on this..!!!

Thanks

Upvotes: 1

Views: 55

Answers (3)

Martin Weitzmann
Martin Weitzmann

Reputation: 4736

Kind of an interesting problem. I came up with this checking if everything is covered and the amount is correct: 

WITH t AS (SELECT * FROM UNNEST(
  [struct(101 as ticket_id, 'assigned' as status),(101,'closed'),
    (102,'assigned'),(102,'resolved'),(102,'closed'),
    (104,'assigned'),(104,'assigned'),(104,'closed'),
    (103,'assigned'),(103,'pending'),(103,'pending'),(103,'assigned'),(103,'resolved'),(103,'closed')]  
  )
)

SELECT ticket_id, array_agg(distinct status) as st 
FROM t
group by 1
having (SELECT count(1)=3 FROM unnest(st) a left join unnest(['assigned','resolved','closed']) b on a=b)

Includes adjusted sample data to cover more problem cases.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

#standardSQL
SELECT ticket_id
FROM `project.dataset.ticket_lc`
GROUP BY ticket_id
HAVING COUNT(DISTINCT status) = 3
AND COUNTIF(LOWER(status) NOT IN ('assigned', 'closed', 'resolved')) = 0

Yo can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.ticket_lc` AS (
  SELECT 101 ticket_id, 'Assigned' status UNION ALL
  SELECT 101, 'Pending' UNION ALL
  SELECT 101, 'Resolved' UNION ALL
  SELECT 101, 'Closed' UNION ALL
  SELECT 102, 'Assigned' UNION ALL
  SELECT 102, 'Resolved' UNION ALL
  SELECT 102, 'Closed' UNION ALL
  SELECT 103, 'Assigned' UNION ALL
  SELECT 103, 'Pending' UNION ALL
  SELECT 103, 'Pending' UNION ALL
  SELECT 103, 'Assigned' UNION ALL
  SELECT 103, 'Resolved' UNION ALL
  SELECT 103, 'Closed' 
)
SELECT ticket_id
FROM `project.dataset.ticket_lc`
GROUP BY ticket_id
HAVING COUNT(DISTINCT status) = 3
AND COUNTIF( LOWER(status) NOT IN ('assigned', 'closed', 'resolved')) = 0

with result 

Row ticket_id    
1   102

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do aggregation : 

select ticket_id
from table t
group by ticket_id
having sum( case when status not in ('assigned', 'closed', 'resolved') then 1 else 0 end ) = 0 and
       count(*) = 3;

If you have a duplicate status for ticket then use distinct inside count().

Upvotes: 1

Related Questions