Home What is the best approach to do this condition in SQL if user has a multiple activity and I only want to get a specific status

Questions

What is the best approach to do this condition in SQL if user has a multiple activity and I only want to get a specific status

byMR

March 22, 2023

Status will only show :

Done – if there is atleast 1 completed

-In Progress –if there is no any completed but atleast 1 in progress

Hold – if all activities is in hold

Sample Data :

MyTable (TableName)

StatusColumn (ColumnName)

Done

In Progress

Hold

I’m thinking to use the CASE() function but I am quite confused since I’m still a beginner in SQL , or if there’s any other approach that you guys can suggest. Thanks.

>Solution :

You can do it using Case Expresssion in Google BigQuery as :

SELECT 
  CASE
    WHEN SUM(CASE WHEN StatusColumn = 'Done' THEN 1 ELSE 0 END) > 0 THEN 'Done'
    WHEN SUM(CASE WHEN StatusColumn = 'In Progress' THEN 1 ELSE 0 END) > 0 THEN 'In Progress'
    ELSE 'Hold'
  END AS Status
FROM MyTable;

You can also do it using COUNTIF function too.

SELECT 
  CASE
    WHEN COUNTIF(StatusColumn = 'Done') > 0 THEN 'Done'
    WHEN COUNTIF(StatusColumn = 'In Progress') > 0 THEN 'In Progress'
    ELSE 'Hold'
  END AS Status
FROM MyTable