Calculating records based on other records contained in a subset of data SQL

Question

I have the following data set:

My subsets of data are determined by the Account column. More than one Customer may belong to one Account column value, as shown in the attached data.

I want the output to be resolved in two ways:

1. If at least one customer on the same account has KEY = 'N', select OUTPUT = 'NOT OK' for all customers assigned to this account

2. Apply the principle as in point 1, but additionally set OTPUT for a given client who was dropped at point 1, OUTPUT = 'NOT OK' on all other account assignments

In two cases, client no. 4 has output = "OK" because it did not occur on any other account where at least 1 other client had KEY = 'N' set

Answer 1

Using SQL Server queries

-- Drop the table if it exists
    DROP TABLE IF EXISTS CustomerData;

-- Create a table to store the data
CREATE TABLE CustomerData (
    Account VARCHAR(10),
    CustomerID INT,
    [KEY] CHAR(1)
);

-- Insert the data into the table
INSERT INTO CustomerData (Account, CustomerID, [KEY])
VALUES
    ('A', 1, 'T'),
    ('A', 2, 'N'),
    ('A', 3, 'T'),
    ('B', 1, 'T'),
    ('B', 3, 'T'),
    ('B', 4, 'T');


-- Query for the first case
-- Common Table Expression (CTE) to calculate per-account status
WITH AccountStatus AS (
    SELECT
        Account,
        MAX(CASE WHEN [KEY] = 'N' THEN 1 ELSE 0 END) AS HasNKey
    FROM CustomerData
    GROUP BY Account
)
-- Main query to join with the CTE and calculate OUTPUT
SELECT
    cd.CustomerID,
    cd.Account,
    CASE
        WHEN A.HasNKey = 1 THEN 'NOT OK'
        ELSE 'OK'
    END AS OUTPUT
FROM CustomerData cd JOIN
    AccountStatus as A ON cd.Account = A.Account;


-- Query for the second case
WITH CTE AS (
    SELECT
        CustomerID,
        Account,
        CASE
            WHEN 'N' IN (SELECT [KEY] FROM CustomerData WHERE Account = cd.Account) THEN 'NOT OK'
            ELSE 'OK'
        END AS OUTPUT
    FROM
        CustomerData cd
)
SELECT
    CustomerID,
    Account,
    CASE
        WHEN 'NOT OK' IN (SELECT OUTPUT FROM CTE WHERE CustomerID = c.CustomerID) THEN 'NOT OK'
        ELSE 'OK'
    END AS OUTPUT
FROM
    CustomerData c;