T-SQL to filter on only condition on group by-CodePudding

My source data is following

DECLARE @t1 AS TABLE
               (
                   sn int,
                   site varchar(max),
                   cond varchar(max),
                   val int
               )

INSERT INTO @t1
   SELECT *
   FROM
       (VALUES
            (1, 'site1', 'X', 100),
            (2, 'site1', 'Y', 200),
            (3, 'site1', 'Z', 300),
            (1, 'site2', 'X', 100),
            (2, 'site2', 'Y', 200),
            (3, 'site2', 'Z', 300),
            (1, 'site3', 'X', 100),
            (2, 'site3', 'P', 200),
            (3, 'site3', 'Q', 300),
            (1, 'site4', 'A', 100),
            (2, 'site4', 'Y', 200),
            (3, 'site4', 'Q', 300),
            (1, 'site5', 'E', 100), 
            (1, 'site5', 'E', 1000),
            (2, 'site5', 'F', 200),
            (3, 'site5', 'G', 300)
         ) t (a, b, c, d)

I am trying to create a calculated column which will test for each set of sites, whether all the conditions are only X,Y or Z

If by site all the values are not X, Y or Z, then it is not desired.

My desired result is following

| site  | cond | test |
|-------|------|------|
| site1 | X    | 1    |
| site1 | Y    | 1    |
| site1 | Z    | 1    |
| site2 | X    | 1    |
| site2 | Y    | 1    |
| site2 | Z    | 1    |
| site3 | X    | 0    |
| site3 | P    | 0    |
| site3 | Q    | 0    |
| site4 | A    | 0    |
| site4 | Y    | 0    |
| site4 | Q    | 0    |
| site5 | E    | 0    |
| site5 | E    | 0    |
| site5 | F    | 0    |
| site5 | G    | 0    |

So far, I tried this but it does not give me what I want. Is there any filtering technique that tests for only sets of value in SQL?

SELECT 
    *,
    MAX(CASE
            WHEN cond = 'X'
                 OR cond = 'Y'
                 OR cond = 'Z' 
                THEN 1
                ELSE 0
        END) OVER (PARTITION BY site) AS test
FROM   
    @t1

CodePudding user response：

Some of your test data has more than 3 sites so working on the assumption your can have more than 3 sharing cond X/Y/Z or would want to exclude a site with more eg X/Y/Z/Q then I suspect you need to compare the cond with the total number of sites, like so:

select site, cond,
    Iif(
        Sum(case when cond in ('X', 'Y', 'Z') then 1 end) over (partition by site) 
        = Count(*) over (partition by site), 1, 0
    ) as test
from t1

CodePudding user response：

We can use window functions for this, to dynamically determine the test column in the result. This solution assumes the pair (site, cond) is unique. If this isn't true, a little more logic may be required.

SELECT *
     , CASE WHEN
                 COUNT(CASE WHEN cond IN ('X', 'Y', 'Z') THEN 1 END) OVER (PARTITION BY site) = 3
             AND COUNT(*) OVER (PARTITION BY site) = 3 THEN 1 ELSE 0 END AS test
  FROM t1
;

Adjusted to test for X, Y, and Z and only those.

Here's the test case:

CodePudding user response：

If I understand correctly:

select t1.*,
       1 - max(case when cond not in ('X', 'Y', 'Z') then 1 else 0 end) as flag
from t1;