How can I add this additional column based on condition

Question

Hi Team,

I want to write a query for my input table and add a additional column on fly if parent question response satisfy the condition mentioned in the child question. Below is sample result that I want to achieve.

Please help.

Answer 1

Try something like this:

select Q.*, 
    case when P.QuestionId is null then 'Y'
    else
        case Q.Condition 
        when '=' then iif(P.QuestionResponse = Q.ParentResponse, 'Y', 'N') 
        when '>' then iif(P.QuestionResponse > Q.ParentResponse, 'Y', 'N') 
        end
    end as ShowQuestion
from Questions Q
left join Questions P on P.QuestionId = Q.ParentQuestionId

It can be extended for more supported conditions.