Group By with multiple condition CASE WHEN in Sql Server-CodePudding

I have a table called DMIntegration like this with many dealerId:

DealerId  |  KPI   |  Value
----------------------------
  001          1        Y
  001          2        Y
  001          3        Y
  001          4        Y
  002          1        Y
  002          2        Y
  002          3        N
  002          4        Y

What I would like to obtain is to group by DealerId and put 1 if all the KPI Value are Y, else 0. How can I specify 1 or 0 based on multiple rows? In this case it would be:

DealerId  |  Result
--------------------
   001         1     
   002         0

This is what I have in mind but I don't kow how to set a value for all the KPI:

SELECT 
    DealerId
    ,CASE WHEN 
                (Value='Y' and KPI=1)    AND
                (Value='Y' and KPI=2)    AND
                (Value='Y' and KPI=3)    AND
                (Value='Y' and KPI=4)    AND        
    THEN 1
    ELSE 0
    END AS Result
FROM DMIntegration
group by DealerId

Edit: In this case I resolved doing this as some of you suggested:

Select DealerId
      ,Result   = min(case when Value = 'Y' then 1 else 0 end)
 From  DMIntegration
 Group By DealerID

But what about If I would like to put Result=1 if KPI in (1,2) with Value ='Y' AND (KPI=3 OR KPI=4) (at least one of the two) with Value ='Y' ?

EDIT 2: Sorry I was trying to simplify the dataset so it was more understandable. So I have n DealerId and for each of them 14 KPI in total and Value field can be Y (Yes) or N (No) for each of the 14 KPIs. What I want to do is to have a table where I have Dealer Id and a Result field that contains a boolean value depending on KPI and Value fields. Result is 1 if KPI 1, 2, 3, 4, 6, 7, 8, 10, 11, 12. 13, 14 are all 'Y' and at least one of the remaining KPI (5 and 9) are equal to 'Y'. Here's an example

DealerId  |  KPI   |  Value
----------------------------
  001          1        Y
  001          2        Y
  001          3        Y
  001          4        Y
  001          5        N
  001          6        Y
  001          7        Y
  001          8        Y
  001          9        N
  001         10       Y
  001         11       Y
  001         12       Y
  001         13       Y
  001         14       Y

In this case I have the table

DealerId       Result
-----------------------------
001                 0

Because 5 and 9 are N. If only 5 or 9 was Y then Result=1

CodePudding user response：

It seems like a simple conditional aggregation should do the trick.

Select DealerId
      ,Result   = min(case when Value = 'Y' then 1 else 0 end)
 From  DMIntegration
 Group By DealerID

CodePudding user response：

Or also ...

WITH
-- your input ..                                                                                                                                                                                               
indata(DealerId,KPI,Value) AS (
          SELECT '001',1,'Y'
UNION ALL SELECT '001',2,'Y'
UNION ALL SELECT '001',3,'Y'
UNION ALL SELECT '001',4,'Y'
UNION ALL SELECT '002',1,'Y'
UNION ALL SELECT '002',2,'Y'
UNION ALL SELECT '002',3,'N'
UNION ALL SELECT '002',4,'Y'
)
SELECT
  dealerId
, CASE COUNT(*)
    WHEN SUM(CASE value WHEN 'Y' THEN 1 ELSE 0 END) THEN 1
    ELSE 0
  END AS result
FROM indata
GROUP BY dealerid;
-- out  dealerId | result 
-- out ---------- --------
-- out  001      |      1
-- out  002      |      0

CodePudding user response：

First draft while on the phone...

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

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=fb993eabdd7a17ff9d4e3cb138b04cb6

Or, a slightly more generalised version...

Each KPI is matched with a group
Each group must have atleast 1 KPI where the value is Y
So, (1, 2, 3, 4, 6, 7, 8, 10, 11, 12, 13, 14) are all in their own group (they all need to be Y)
And (5, 9) are both in group 5 (one of which needs to be Y)

Create the mapping of conditions...

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)
)

Then so the join and count distincts...

SELECT
  d.DealerId,
  CASE WHEN
    COUNT(DISTINCT f.group_id)
    =
    (SELECT COUNT(DISTINCT group_id) FROM filter)
  THEN
    1
  ELSE
    0
  END
    AS Result
FROM
  DMIntegration   AS d
LEFT JOIN
  filter          AS f
    ON  f.kpi   = d.kpi
    AND f.value = d.value
GROUP BY
  d.DealerId

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e4bdf712bc098385b75d16a6c0717186