sql server - T-SQL : Loop within CASE Statement -

i need create new column following respected:

• if all order_id within one id have approved flag of 1 , denied flag of 0, flag approved. (example : id 1)

• if all order_id within one id have approved flag of 0 , denied flag of 1, flag denied. (example : id 2)

• if some order_id within one id have approved flag of 1 , denied flag of 0 , others either approved flag of 0 , denied flag of 1 or approved flag of 0 , denied flag of 0, flag partially approved. (example : id 3 , 6)

• if any order_id within one id have approved flag of 1 , denied flag of 1 regardless of other order_id within id , flag error. (example : id 4)
• if all order_id within one id have approved flag of 0 , denied flag of 0, flag pending approval. (example : id 5)

this simplified table using example:

 **id       order_id   approved   denied    1          101         1         0    2          201         0         1    2          202         0         1    1          102         1         0    4          401         0         1    3          301         0         1    6          601         0         1    1          103         1         0    3          302         1         0    3          303         0         0    5          501         0         0    4          402         1         0    6          602         1         0    5          502         0         0    4          403         1         1    5          503         0         0 

and result expect :

 **id       order_id   approved   denied   approval_status     1          101         1         0        approved    2          201         0         1         denied    2          202         0         1         denied    1          102         1         0        approved    4          401         0         1         error    3          301         0         1    partially approved    6          601         0         1    partially approved    1          103         1         0        approved    3          302         1         0    partially approved    3          303         0         0    partially approved    5          501         0         0     pending approval    4          402         1         0        error    6          602         1         0    partially approved    5          502         0         0     pending approval    4          403         1         1        error    5          503         0         0     pending approval 

thank in advance !! confused of how loop within 1 id in case statement.

i take comment above.

with cte (     select         count(*) numberoforder         , sum(approved) numberofapproved         , sum(denied) numberofdenied         , sum(iif(approved=1 , denied=1,1,0)) numberoferror         , id             testtable t1     group         t1.id ) select     t2.*     , case         when cte.numberoferror > 0 'error'         when cte.numberofapproved=cte.numberoforder 'approved'         when cte.numberofdenied=cte.numberoforder 'denied'         when cte.numberofapproved>0 or cte.numberofdenied > 0 'partially approved'         else 'pending approval'     end approval_status     testtable t2     join cte on t2.id=cte.id