CODEWITHSUNDEEP
sqlsql-serverwhere-clausewindow-functions
Pas Palter
Pas Palter

Reputation: 3

SQL Count condition in main Query

I want to pick on those who for the column is_initial_event: the values can be 0 or 1. I only want those who have only 1. Not that they have both 1 and 0. There are multiple detail rows for is_initial_event.

select ac.Full_name, pe.program_start_date, pe.program_end_date, tp.approved_by_Description as 
    Approved, ev.is_initial_event, tp.staff 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

Here I tried to add in the count, what I want to count is that if the people_id has > 0 for ev.Is_initial_event = 1 and also has > when ev.IS_initial_event = 0, then I don't want that people_id only when it only has records where ev.is_initial_event = 1 not both.

Upvotes: 0

Views: 71

Answers (1)

GMB
GMB

Reputation: 222622

Are you looking for window functions?

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

This returns rows for which all rows having the same people_id have initial_event set to 1 (note that this assumes that this column contains only 0s and 1s, as explained in your question).

Upvotes: 1

Related Questions