Need to be able to change value to filter in a query based on the value of another field

Question

I have a control which contains one of the following 3 values:

1. Q Research
2. T Research
3. B Research

I have a 2nd query which will use a value based on the above to search the Status from the Status history section.  The Status of the above is "Complete" but the Status I'm looking for is "Q - Complete" or "T- Complete" or "B - Complete".
Task level status will contain "Complete"
Sub-task level status will be a little different based on the top 3 values.
Is there a way to change the Status value I'm searching for with an IF statement or CASE statement.

Task:
RQ_Type = T Research
Status = Complete

Sub-task:
RQ_Type = T Research
Status = T - Complete

I need to be able to search for all Sub-tasks where the Sub-task Status is based on the value of the RQ_Type.

Jessie Turpin · Answer

Hi Brant! Thanks for your question and sharing your SQL code. You could certainly use a CASE statement to modify your subtask issue's status, however I'd recommend adding the CASE statement into the SELECT statement instead of having it in the WHERE clause. Also, you'll need to follow the Spark SQL syntax formatting for CASE statements which is

CASE WHEN x THEN y ELSE z END

If you're going to be querying this subtask status often, you may want to look into adding this as a custom column or custom table in your schema.

Brant Ramey · Answer

Here is a copy of my SQL:

SET TIME ZONE 'UTC';SELECT `Issue`.`issue_id` AS `Issue ID`,       `Issue status history`.`status` AS `Status`,       DATE_FORMAT(`Issue status history`.`started_at`, 'yyyy-MM-dd') AS `Day of Started at`FROM `jira_issue` AS `Issue`INNER JOIN `jira_issue_status_history` AS `Issue status history` ON `Issue status history`.`issue_id` = `Issue`.`issue_id`INNER JOIN `jira_project` AS `Project` ON `Issue`.`project_id` = `Project`.`project_id`WHERE (`Project`.`name` = 'Workflow')  AND (`Issue`.`issue_type` = 'Sub-task')  CASE   WHEN ({TEXT_INPUT_REQUEST_TYPE} = 'Technical Research')THEN  AND (`Issue status history`.`status` = 'Tech - Complete')WHEN ({TEXT_INPUT_REQUEST_TYPE} = 'Qualitative Research')THENAND ('Issue status history','status' = 'Qual - Complete')ELSEAND ('Issue status history','status' = 'Quant - Complete')END  AND `Issue status history`.`started_at` BETWEEN (CURRENT_TIMESTAMP - INTERVAL 1 MONTH) AND CURRENT_TIMESTAMPGROUP BY `Issue`.`issue_id`,         `Issue status history`.`status`,         DATE_FORMAT(`Issue status history`.`started_at`, 'yyyy-MM-dd')ORDER BY `Issue`.`issue_id` ASC,         `Issue status history`.`status` ASC,         DATE_FORMAT(`Issue status history`.`started_at`, 'yyyy-MM-dd') ASCLIMIT 1000;