Calculating Main Current Task column by concatenating 2 task names based on multiple conditions

Copper Contributor

Hello, I am trying to solve the problem if someone can help that would be appreciated. I am beginning my journey with SQL.


Desired Output - Main Current Task Column


Task ID column is in ASCENDING order, and each language as a group has multiple Parent tasks.  


1st Condition


I am trying to calculate Main Current Task Column in such a way where ever Tsk Status is Delivered and Project_Deliverable_Not_Deliverable is 'Deliverable' between one Parent and other parent (i.e., Column - Parent_Find) within a same language group it concatenates the Task of Parent + Task from the row which as Delivered status from Tsk Status column (i.e., Task 3) and gives Main Current Task as Task 1 + Task 3


Likewise Task 4 + Task 9 for second Parent within same English language.


2nd if CONDITION understanding 


Can be any langauge, but For example lets say for Czech, as a language group Tsk Status is delivered for Task B but Project_Deliverable_Not_Deliverable is 'Non-Deliverable' hence no concatenation with Parent task as earlier just Task B.

and Task D as a output within same Czech group as new parent is present, (order by ASC Tsk Id column) because Tsk Status is delivered, Project_Deliverable_Not_Deliverable is deliverable and Parent at a same time, hence only picking Task D.


I was thinking to develop a logic saying where Parent Find = Parent and Tsk Id < Tsk Id of 2nd Parent within same language group and write if else statements to pick the relative task from  Task Type column and then concatenate with Task of the parent. 


Issue is there can be multiple parent within a language group order by Tsk Id ASC


Any help with the logic is helpful. Thanks.


Job IDLangaugeTask TypeParent_FindProject IdTsk IdTsk StatusMain Current TaskProject_Deliverable_Not_Deliverable
12345 EnglishEnglishTask 1Parent123458206892FinishedTask 1 + Task 3Deliverable
12345 EnglishEnglishTask2 123458206893FinishedTask 1 + Task 3Deliverable
12345 EnglishEnglishTask3 123458206894DeliveredTask 1 + Task 3Deliverable
12345 EnglishEnglishTask 4Parent123458206895FinishedTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 5 123458206896FinishedTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 6 123458206897FinishedTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 7 123458206898FinishedTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 8 123458206899FinishedTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 9 123458206900DeliveredTask 4 + Task 9Deliverable
12345 EnglishEnglishTask 10 123458206901CanceledTask 4 + Task 9Deliverable
23412 CzechCzechTask AParent234128206902CanceledTask BDeliverable
23412 CzechCzechTask B 234128206903DeliveredTask BNon-Deliverable
23412 CzechCzechTask C 234128206904CanceledTask BDeliverable
23412 CzechCzechTask DParent234128206905DeliveredTask DDeliverable
23412 CzechCzechTask E 234128206906DeliveredTask DDeliverable
23412 CzechCzechTask FParent234128206907FinishedTask F + Task IDeliverable
23412 CzechCzechTask G 234128206908FinishedTask F + Task IDeliverable
23412 CzechCzechTask H 234128206909FinishedTask F + Task IDeliverable
23412 CzechCzechTask I 234128206910DeliveredTask F + Task IDeliverable
2 Replies

Based on the provided description and sample data, it seems that you are looking for a logic to calculate the "Main Current Task" column based on certain conditions. Here's a possible approach to achieve that:

Create a temporary table or view with the necessary columns: Job ID, Language, Task Type, Parent_Find, Tsk Id, Tsk Status, and Project_Deliverable_Not_Deliverable.

Use the following SQL query to calculate the "Main Current Task" column:

t1.`Job ID`,
t1.`Task Type`,
t1.`Tsk Id`,
t1.`Tsk Status`,
CONCAT(t1.`Task Type`,
WHEN t1.`Tsk Status` = 'Delivered' AND t1.`Project_Deliverable_Not_Deliverable` = 'Deliverable' THEN
(SELECT CONCAT(' + ', t2.`Task Type`)
FROM your_table_name t2
WHERE t2.`Language` = t1.`Language`
AND t2.`Parent_Find` = t1.`Parent_Find`
AND t2.`Tsk Status` = 'Delivered'
AND t2.`Project_Deliverable_Not_Deliverable` = 'Deliverable'
AND t2.`Tsk Id` > t1.`Tsk Id`
ORDER BY t2.`Tsk Id` ASC
) AS `Main Current Task`,
FROM your_table_name t1

Replace your_table_name with the actual name of the table or view you created in step 1.

This query uses a subquery to find the next delivered task with deliverable project status within the same language group and parent. It concatenates the current task with the found task using the CONCAT function.

Note: Make sure to adjust the column names and table/view names in the query based on your actual schema.
Hope this will help you.
Please post table design as DDL, some sample data as DML statement and the expected result.