sql server - Case statement based upon multiple column values -

i'd either add case statement return y/n 'all complete?' column or exclude rows 'all complete' = 'y'.

in order 'all complete' set y, id_status's need complete each customer , each code number. each customer can have multiple id's or code_number. i'm having trouble understanding case logic across multiple columns values aren't same. help.

idtable: id   id_status customer  code_number  complete?  1    complete  alex      123          y 2    complete  alex      123          y 3    complete  brian     321          y 4    complete  brian     321          y 5    open      brian     425          n 6    complete  brian     425          n 7    open      charlie   123          n 8    complete  charlie   123          n 9    open      charlie   123          n 10   complete  donald    555          n 11   complete  donald    555          n 12   testing   donald    555          n 13   complete  eric      620          y 

assuming sql server 2008+:

with cte (     select  *,             count(*) over(partition customer, code_number) total,             sum(case when id_status = 'complete' 1 else 0 end) over(partition customer, code_number) completed     dbo.yourtable ) select  id,         id_status,         customer,         code_number,         case when total = completed 'y' else 'n' end [all complete] cte;