Partition by issue

hello, I am struggling to partition a column based on products. I tried with partition by but it doesnt work as i would like to work.
I have a table with 3 columns: Product, Sub Product and Flag. I need to populate the Flag column.
The list of Sub Products which is important for me is 4J, UW and 5V. The problem is that some of my products besides these Sub products have other sub products.
I want to populate the Flag column with "Passed" where the product has Sub products only in this category( 4J, UW or 5V) as i populated the Flag column for Product = D.
If the product doesnt have at least one of these sub products then i populate for that product with N (example product C).
If the product has a mixture with those 3 Sub products and other sub products which are not important then i need to populate with Y (example products A and B). The list of the sub products important will not change, but the sub products which are not important will change always, i just gave some examples.
image

I will really appreciate any help possible.
thank you!

Something like:

select product
      ,[sub product]
      ,case
          when sum(case
                      when [sub product] in ('4J','UW','5V')
                      then 1
                      else 0
                   end
                  )
               over(partition by product)
              =3
          then 'passed'
          when sum(case
                      when [sub product] in ('4J','UW','5V')
                      then 1
                      else 0
                   end
                  )
               over(partition by product)
              >0
          then 'Y'
          else 'N'
       end
       as flag
  from yourtable
;

thank you for your response but for example if my product has only 4J and 5V, it will go under "Y" instead of "passed". The "passed" section has to have only sub products from ('4J','UW','5V') but it doesnt have to be all 3, it can be only 2 or 1.

select product
      ,[sub product]
      ,case
          when sum(case
                      when [sub product] in ('4J','UW','5V')
                      then 1
                      else 0
                   end
                  )
               over(partition by product)
              =0
          then 'N'
          when sum(case
                      when [sub product] in ('4J','UW','5V')
                      then 0
                      else 1
                   end
                  )
               over(partition by product)
              >0
          then 'Y'
          else 'passed'
       end
       as flag
  from yourtable
;
1 Like

thank you