By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DMIntegration (
DealerId INT,
kpi INT,
value CHAR(1)
)
INSERT INTO
DMIntegration
VALUES
(1, 1, 'Y'),
(1, 2, 'Y'),
(1, 3, 'Y'),
(1, 4, 'Y'),
(1, 5, 'N'),
(1, 6, 'Y'),
(1, 7, 'Y'),
(1, 8, 'Y'),
(1, 9, 'N'),
(1, 10, 'Y'),
(1, 11, 'Y'),
(1, 12, 'Y'),
(1, 13, 'Y'),
(1, 14, 'Y')
14 rows affected
SELECT
DealerId,
CASE
WHEN
COUNT(
DISTINCT
CASE
WHEN [value] = 'Y'
AND [kpi] IN (1, 2, 3, 4, 6, 7, 8, 10, 11, 12, 13, 14)
THEN [kpi]
END
)
= 12
AND
COUNT(
DISTINCT
CASE
WHEN [value] = 'Y'
AND [kpi] IN (5, 9)
THEN [kpi]
END
)
> 0
THEN
1
ELSE
0
END
AS Result
FROM
DMIntegration
GROUP BY
DealerId
| DealerId | Result |
|---|---|
| 1 | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.