How to put single flag if same cust have different account

Amitesh Verma

9y1.1k

Hi coder,

Let me explain what is my requirment. I need to check if i have customers with different productid if yes then i have to check if they have any producttype null or not in case any of the customerid have NULL producttype then flag for both the customerid should be N else Y

For example :I have a table in which i have many columns. PFB the table stucture

Customerid productid producttype

1 a x

1 b Null

2 c y

2 d y

3 e z

3 f Null

what i want is like below:

Customerid Productid Productype flag

1 a x N

1 b Null N

2 c y Y

2 d y Y

3 e z N

3 f Null N

till now what i have done

;with cte as

(

select * from test where customerid in

(select customerid from test group by customerid having count(*) >1

) )

from this i collect all the customerid who have more then one productid and different producttpe now i want to add the flag part.

Please let me know if this approach is good and how can i achieve the next thing.

Thanks in advance !!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhilash J A9y ago
Something like this should work:
WITH cte As
(
    SELECT
        CustomerId,
        Count(1) As Num,
        Max(CASE WHEN ProductType Is Null THEN 1 ELSE 0 END) As AnyNulls
    FROM
        Test
    GROUP BY
        CustomerId
)
SELECT
    T.CustomerId,
    T.ProductId,
    T.ProductType,
    CASE
        WHEN S.AnyNulls = 0 THEN 'Y'
        WHEN S.Num > 1 THEN 'N'
        ELSE Null
    END As Flag
FROM
    Test As T
    INNER JOIN cte As S
    ON S.CustomerId = T.CustomerId
;
The flag will be Y if none of the product types for the customer are Null; N if there is more than one product and at least one product type is Null; and Null if there is only one product, and the product type is Null.

If you need to change that, you should be able to modify the second CASE statement.
Anil Jha9y ago
I thinkk it should be 'Y' if there is only one product for customerid and product type is not null. I have updated sql.
Abhilash J A9y ago
What if they only have one product? What should the flag be set to in that case?
Anil Jha9y ago
Amitesh,
Try to run below sql. It gives the same result that you are expecting.
SELECT CustomerID, ProductID, ProductType,
CASE
    WHEN (SELECT COUNT(*)
          FROM Test T2
          WHERE T2.customerID = T1.CustomerID ) = 1 AND ProductType IS NOT NULL THEN 'Y'
    WHEN (SELECT COUNT(*)
          FROM Test T2
          WHERE T2.customerID = T1.CustomerID
          AND ProductType IS NOT NULL) > 1 THEN 'Y'
    ELSE 'N'
END AS Flag
FROM Test T1
Thanks