Grouping items and setting a flag-CodePudding

I have a table structured as follows:

order_yr    acct_id indv_id age
2019        323     01      38
2019        323     02      37
2019        323     03      16
2019        323     04      5
2019        325     01      38
2019        326     01      64
2019        326     02      63

What I need to do is by order_yr and acct_id add a flag if the order_yr and acct_id has someone age <=17.

The result would be like this:

order_yr    acct_id indv_id age child_flg
2019        323     01      38  1
2019        323     02      37  1
2019        323     03      16  1
2019        323     04      5   1
2019        325     01      38  0
2019        326     01      64  0
2019        326     02      63  0

I know I have to partition by order_yr and acct_id, but not sure how to get the result in one inline script.

Any help would be appreciated.

BTW this is an individual level extract with a number of other columns associated with each indv.

I've not gotten very far - I have this:

,ROW_NUMBER() OVER(PARTITION BY order_yr, acct_id ORDER BY (CASE WHEN age <=17 THEN 'Y' ELSE 'N' END) desc) AS CHILD_flg

CodePudding user response：

You have some options here. One is using a subquery to find out if a row exists that belongs to a group and meets your condition:

select * 
     , case
         when exists (select * 
                        from #data sub
                       where sub.order_yr = d.order_yr
                         and sub.acct_id = d.acct_id
                         and sub.age <= 17)
         then 1
         else 0
       end as flag
  from #data d

You can also go with a window function like you planned:

 select * 
      , max(case when age <= 17 then 1 else 0 end) over (partition by order_yr, acct_id) as flag
  from #data d

Working demo on dbfiddle