CODEWITHSUNDEEP
sqlsql-server
Dee
Dee

Reputation: 33

How do I filter out a group where there is at least one occurrence of a value?

In sample data below I need to show only records where the ContactType is not equal to 'CLOSR' by caseid_i but with my code below it filters out anything with CLOSR, but not by the CaseID_I. So if at least one of the Caseid_i has a ContactType of 'CLOSR' I need to filter out the whole CaseID. So for CaseID_i 51709, that caseid_i wouldn't show up in my results at all because at least one of the 'ContactTypes' is CLOSR and the same for 51715.

enter image description here

SELECT C.[cdcpincid_c]
    ,I.[caseid_i]
    ,I.echono_c
    ,C.[close_d] AS ClosureDate
    ,C.[clinician_c] AS ClosureClinician
    ,N.[contacttyp_c] AS ContactType
    ,SC.StaffName AS ClosureStaffName
    ,I.Refdate_d
FROM [cd].[tb_cdcp_case] C
INNER JOIN [cd].[tb_cdcp_incident] I ON I.[uniqueid_c] = C.[cdcpincid_c]
INNER JOIN [cd].[tb_cdcp_clientcontact] N ON I.[uniqueid_c] = N.[cdcpincid_c]
LEFT OUTER JOIN [dbo].[vCDCP_Staff] SC ON C.clinician_c = SC.Staffcode_c
GROUP BY C.[cdcpincid_c]
    ,I.[caseid_i]
    ,I.echono_c
    ,C.[close_d]
    ,C.[clinician_c]
    ,N.[contacttyp_c]
    ,SC.StaffName
    ,I.Refdate_d
HAVING sum(CASE 
            WHEN N.[contacttyp_c] = 'CLOSR'
                THEN 1
            ELSE 0
            END) = 0
    AND C.[close_d] IS NOT NULL
    AND I.echono_c <> 'OC'

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Remove the condition from the where clause and switch it to a having clause:

having sum(case when ContactType = 'CLOSR' then 1 else 0 end) = 0

EDIT:

I see. You are returning multiple rows per caseid. In that case, use a window function as well:

having sum(sum(case when ContactType = 'CLOSR' then 1 else 0 end)) over (partition by caseid) = 0

Upvotes: 1

Related Questions