SQL query for evaluating boolean logic from innerjoin on itself

user_603260 0
Viewed 38

Trying to determine a PASS/FAIL result from dataset where the expected number of FLAG values in a column is predetermined, and another column of the same rows match specific text.

If the number of values found in the Descript column equals the predetermined number AND the PValue of each of those rows match the text 'PASS' then the result is text 'PASS'.

If the number of values found does not equal the predetermined number, the result is text 'FAIL' regardless of the PValue content.

If number of values found equal the predetermined number but one or more of the PValue entries does not equal 'PASS' then the result text is 'FAIL'.

Example input data with expected number of FLAG rows = 3:

Descript PValue uniqueID
Last year 1
FLAG PASS 2
Master product 3
FLAG PASS 4
FLb PASS 5
Prime Minister 6
Laurie Quiz 7
FLAG PASS 8
Hugh Who 9

expected result of above data:

count
PASS

else the output:

count
FAIL

This is my query so far:

DECLARE @ExpectedNumFlags AS INT = 3

  select count(*) as 'count'
  FROM
(
SELECT [Descript]
      ,[PValue]
      ,[uniqueID]
  FROM [MyBookLibraryDB].[dbo].[Historian]
  WHERE [Descript] = 'FLAG'
) AS t
INNER JOIN
(
  select count(*) as 'count'
  FROM
  (
    SELECT [Descript]
      ,[PValue]
      ,[uniqueID]
    FROM [MyBookLibraryDB].[dbo].[Historian]
    WHERE [Descript] = 'FLAG'
  ) AS z
 WHERE [PValue] = 'PASS'
 ) AS q
 on q.count = @ExpectedNumFlags -- this doesn't make sense

The last ON clause was just me flailing around trying to noodle this... and realizing I need help.

Please advise. Thanks (after painstakingly formatting the data per SO table guidance, the preview shows nice tables, then the post looks like garbage!)

sql-server sql
1 Answers

If the number of values found in the Descript column equals the predetermined number AND the PValue of each of those rows match the text 'PASS' then the result is text 'PASS'.

If I understand correctly, you only care about FLAG rows. And you want to know if all flag rows are 'PASS' and have the expected number. If so, this is conditional logic on aggregate amounts:

select (case when sum(case when pvalue = 'PASS' then 1 else 0 end) = count(*) and
                  count(*) = <predetermined number>
             then 'PASS' else 'FAIL'
        end)
from [MyBookLibraryDB].[dbo].[Historian]
where desript = 'FLAG'
user_11440350
Related