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

[Brant Ramey]

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]

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]

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')

THEN

AND ('Issue status history','status' = 'Qual - Complete')

ELSE

AND ('Issue status history','status' = 'Quant - Complete')

END

  AND `Issue status history`.`started_at` BETWEEN (CURRENT_TIMESTAMP - INTERVAL 1 MONTH) AND CURRENT_TIMESTAMP

GROUP 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') ASC

LIMIT 1000;