Using partition but need to add a condition

[pk400i]

What we have here is that the column is_initial_event we have coded that only the people_id that have only '1' value in is_initial_event (not 0 and 1) we want, But I also need the ones that are null - how can I fold in the Null requirement in the partition?
select *
from (
select
ac.Full_name,
pe.program_start_date,
pe.program_end_date,
tp.approved_by_Description as Approved,
ev.is_initial_event,
tp.staff,
min(ev.is_initial_event + 0) over(partition by ac.people_id) min_initial_event

from all_clients_view ac  
inner join program_enrollment_expanded_view pe
    on ac.people_id = pe.people_id
inner join service_plan_goals_objectives_methods_view tp
    on ac.people_id = tp.people_id
inner join service_plan_event_view ev
    on ac.people_id = ev.people_id
where 
    pe.program_name = 'CFTSS' 
    and tp.Approved_by_description is null

) t
where min_initial_event = 1

order by full_name

[harishgg1]

please see if this below link helps ..

SQL Server - Behaviour of ROW_NUMBER Partition by Null Value

[mike01]

please post DDL and sample data

[pk400i]

Currently all rows returned are like this:
For the is_inital_event this code will only return 1, that they don't also have 0 in one of their rows,
But some we would also want to return

But some might be Null in that column. If they are null and also not a 0 and 1 then we also want those.

[yosiasz]

please post sample data or give us direct access to your sql server?

create table #pk400i(id int, name nvarchar(50))

insert into #pk400
select 1, 'mike' union
select 2, 'pk' union
select 3, 'yosi'

[harishgg1]

hi

i am having a tough time understanding ....

can you please explain like this !!