SQL logic to fail a check if any of the related customers has failed

user_16413284 0
Viewed 54

I have the requirement to flag the customers Y only when all the related customers have also passed the check. below are the two tables:

relationship table :

customer_id related_customer
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
11 11
11 22
22 11
22 22

Check table

customer_id check_flag
1 y
2 y
3 n
11 y
22 y

I want output like below:

customer_id  paas_fail_flag
1 n
2 n
3 n
11 y
22 y

output justification: since 1,2,3 are related customers and since one of them (3) has n in table 2 , so all the related customers should also have n. 11,22 are related customers and both have y in table 2.so in output both should have y.

count teradata join oracle sql
3 Answers

Something like this? Sample data in lines #1 - 40; query begins at line #41:

SQL> WITH
  2     -- sample data
  3     rel (customer_id, related_customer)
  4     AS
  5        (SELECT 1, 1 FROM DUAL
  6         UNION ALL
  7         SELECT 1, 2 FROM DUAL
  8         UNION ALL
  9         SELECT 1, 3 FROM DUAL
 10         UNION ALL
 11         SELECT 2, 1 FROM DUAL
 12         UNION ALL
 13         SELECT 2, 2 FROM DUAL
 14         UNION ALL
 15         SELECT 2, 3 FROM DUAL
 16         UNION ALL
 17         SELECT 3, 1 FROM DUAL
 18         UNION ALL
 19         SELECT 3, 2 FROM DUAL
 20         UNION ALL
 21         SELECT 3, 3 FROM DUAL
 22         UNION ALL
 23         SELECT 11, 11 FROM DUAL
 24         UNION ALL
 25         SELECT 11, 22 FROM DUAL
 26         UNION ALL
 27         SELECT 22, 11 FROM DUAL
 28         UNION ALL
 29         SELECT 22, 22 FROM DUAL),
 30     chk (customer_id, check_flag)
 31     AS
 32        (SELECT 1, 'y' FROM DUAL
 33         UNION ALL
 34         SELECT 2, 'y' FROM DUAL
 35         UNION ALL
 36         SELECT 3, 'n' FROM DUAL
 37         UNION ALL
 38         SELECT 11, 'y' FROM DUAL
 39         UNION ALL
 40         SELECT 22, 'y' FROM DUAL),

 41     temp
 42     AS
 43     -- minimum CHECK_FLAG per customer and related customer
 44        (  SELECT r.customer_id, r.related_customer, MIN (c.check_flag) mcf
 45             FROM rel r JOIN chk c ON c.customer_id = r.related_customer
 46         GROUP BY r.customer_id, r.related_customer)
 47    SELECT customer_id, MIN (mcf) flag
 48      FROM temp
 49  GROUP BY customer_id
 50  ORDER BY customer_id;

CUSTOMER_ID FLAG
----------- ----
          1 n
          2 n
          3 n
         11 y
         22 y

SQL>
user_90979060

You need to join relationship to check and use conditional aggregation:

SELECT r.customer_id,
       COALESCE(MAX(CASE WHEN c.check_flag = 'n' THEN c.check_flag END), 'y') paas_fail_flag  
FROM relationship r INNER JOIN "check" c 
ON c.customer_id = r.related_customer
GROUP BY r.customer_id
ORDER BY r.customer_id

See the demo.

user_104988280

Assuming that your relationship data could be sparse, for example:

CREATE TABLE relationship ( customer_id, related_customer ) AS
SELECT  2,  3 FROM DUAL UNION ALL
SELECT  3,  1 FROM DUAL UNION ALL
SELECT  3,  2 FROM DUAL UNION ALL
SELECT 11, 22 FROM DUAL;

CREATE TABLE "CHECK" ( customer_id, check_flag ) AS
SELECT  1, 'y' FROM DUAL UNION ALL
SELECT  2, 'y' FROM DUAL UNION ALL
SELECT  3, 'n' FROM DUAL UNION ALL
SELECT 11, 'y' FROM DUAL UNION ALL
SELECT 22, 'y' FROM DUAL;

(Note: The below query will also work on your dense data, where every relationship combination is enumerated.)

Then you can use a hierarchical query:

SELECT customer_id,
       MIN(check_flag) AS check_flag
FROM   (
  SELECT CONNECT_BY_ROOT(c.customer_id) AS customer_id,
         c.check_flag AS check_flag
  FROM   "CHECK" c
         LEFT OUTER JOIN relationship r
         ON (r.customer_id = c.customer_id)
  WHERE  CONNECT_BY_ISLEAF = 1
  CONNECT BY NOCYCLE
         (  PRIOR r.related_customer = c.customer_id
         OR PRIOR c.customer_id = r.related_customer )
  AND    PRIOR c.check_flag = 'y'
)
GROUP BY
       customer_id
ORDER BY
       customer_id

Which outputs:

CUSTOMER_ID CHECK_FLAG
1 n
2 n
3 n
11 y
22 y

db<>fiddle here

user_15092640
Related