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'),
(2, 1, 'Y'),
(2, 2, 'Y'),
(2, 3, 'Y'),
(2, 4, 'Y'),
(2, 5, 'N'),
(2, 6, 'Y'),
(2, 7, 'Y'),
(2, 8, 'Y'),
(2, 9, 'y'),
(2, 10, 'Y'),
(2, 11, 'Y'),
(2, 12, 'Y'),
(2, 13, 'Y'),
(2, 14, 'Y')
;
28 rows affected
WITH
filter
AS
(
SELECT
*
FROM
(
VALUES
( 1, 'Y', 1),
( 2, 'Y', 2),
( 3, 'Y', 3),
( 4, 'Y', 4),
( 5, 'Y', 5), -- This and 9 have the same filter_group_id, only one needs be true
( 6, 'Y', 6),
( 7, 'Y', 7),
( 8, 'Y', 8),
( 9, 'Y', 5), -- This and 5 have the same filter_group_id, only one needs be true
(10, 'Y', 10),
(11, 'Y', 11),
(12, 'Y', 12),
(13, 'Y', 13),
(14, 'Y', 14)
)
AS filter(kpi, value, group_id)
)
SELECT
d.DealerId,
CASE WHEN
COUNT(DISTINCT f.group_id)
=
(SELECT COUNT(DISTINCT group_id) FROM filter)
THEN
1
ELSE
0
| DealerId | Result |
|---|---|
| 1 | 0 |
| 2 | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.