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
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