Assigning a 'Y/N' for each StudyId-CodePudding

I am using SQL Server 2016.

I am working on assigning a 'Y' or 'N' to a column GLP with the following condition:

a 'Y' if one of the GLP values = 'FDA'. It is possible that one StudyId has GLP = FDA but then have GLP = not applicable as well but most won't fall into this category. In this instance, it would still be a 'Y' since it has 'GLP - FDA'.

I have this code which successfully gets the GLP types and assigns a 'Y' or 'N' for each study:

SELECT DISTINCT 
    StudyId,
    GLP = CASE
              WHEN GLP IN ('non-GLP', 'Not Applicable') 
                   OR GLP IS NULL 
                  THEN 'N'
                  ELSE 'Y'
          END
FROM 
    AgenciesTable

This code returns:

StudyId	GLP
123456	Y
123456	N
456789	N
123456	Y

I want:

StudyId	GLP
123456	Y
456789	N

But I am lost as to what I would do to get just one 'Y' or one 'N' per StudyId with the conditions I have above. I have tried doing a subquery which would be something like:

SELECT 
    StudyId, 
    COUNT(GLP) AS TOT = CASE
                            WHEN TOT >= 2 THEN 'Y'
                            ELSE 'N'
                        END
FROM 
    (SELECT DISTINCT 
         StudyId,
         GLP = CASE
                   WHEN GLP IN ('non-GLP', 'Not Applicable') 
                        OR GLP IS NULL 
                       THEN 'N'
                       ELSE 'Y'
               END
     FROM AgenciesTable AT)

No luck with the above but I feel I am headed in the right direction. Not sure what to do to advance this. I might be missing something simple or overthinking this but I cannot for the life of me, figure it out.

Any help, clarification or general ideas are appreciated. Thanks!

CodePudding user response：

Why not use a conditional aggregation ?

Example

SELECT StudyId
      ,GLP = max( CASE WHEN GLP IN ('non-GLP', 'Not Applicable') OR GLP IS NULL THEN 'N'  ELSE 'Y'  END)
FROM AgenciesTable
Group By StudyId

Results

StudyId GLP
123456  Y
456789  N