Search code examples
sql-servert-sqlpartitionpartition-by

Selecting a Common ID where that ID Matches Certain Criteria

In the screenshot, Id is the primary key. There could be many of the same Participant__c values in this result set, each with a different Id.

What I'm trying to do is write a query that will return unique Participant__c values IF that particular Participant__c has records in this result set where the Survey_Index_Number__c values are 2001, 2003, and 2005, and their corresponding Completion_Status__c values are Complete. If Participant__c values MATCH that criteria, return that Participant__c value in a result set.

I'm just having a hard time figuring out the best way to approach this, and I'm leaning towards leveraging partitioning logic to do this, but I'm unsure how to start that.

EDIT: My resolution here. This query stores the current count of completed surveys by ContactId:

SELECT
    Participant__c as 'ContactId',
    COUNT(DISTINCT Survey_Index_Number__c) AS 'SurveyCountComplete'
FROM
    (SELECT
        Participant__c,
        Survey_Index_Number__c,
        Completion_Status__c
    FROM
        Contact_Salesforce c
    INNER JOIN
        Survey_Result__c_Salesforce sr on sr.Participant__c = c.Id
    WHERE
        sr.Completion_Status__c = 'Complete'
        AND Survey_Index_Number__c IN ('2001', '2003', '2005')) as CompletedSurveys
GROUP BY
    Participant__c

enter image description here

Solution

  • Assumed that you want unique Participant__c values if the table/result set has at least one row for each Survey_Index_Number__c values: 2001, 20023, 2005 with Completion_Status__c='Complete':

    select Participant__c
from mytbl a
where  Survey_Index_Number__c in ('2001','2003','2005') 
and Completion_Status__c='Complete'
group by Participant__c
having count(distinct Survey_Index_Number__c )=3