SQL Server : how do do HAVING criteria on bit value

Question

The below code demonstrates the problem I'm trying to solve. Basically I only want to select transactions where a customer has never purchased a product where isActive is now  0.
I tried using GROUP BY and HAVING MIN(p.isActive) = 1 to get only the customers associated with active products. This query is close but MIN does not like bit type. How can I do this?
The goal is to only see transactions associated with just Lisa and Fred. Bill should be removed from the results since one of his transactions is currently associated with a InActive product.
CustomerName    ProductName (No column name)
Bill    Cheerios    1
Lisa    Cheerios    1
Bill    Corn Flakes 1
Fred    Corn Flakes 1
Bill    Granola     0

ERROR: Operand data type bit is invalid for min operator.
CREATE TABLE #Customer (
    CustomerId int,
    CustomerName nvarchar(100),
    Address nvarchar(100),
)
INSERT INTO #Customer
VALUES (1, 'Bill', '123 1st St'),
       (2, 'Fred', '111 Market St'),
       (3, 'Lisa', '01 Boulevard')

CREATE TABLE #Product (
    ProductId int,
    ProductName nvarchar(100),
    isActive bit
)
INSERT INTO #Product
VALUES (1, 'Corn Flakes', 1),
       (2, 'Cheerios', 1),
       (3, 'Granola', 0)

CREATE TABLE #TransactionLog (
    LogId int,
    ProductId int,
    CustomerId int,
    Amount float
)
INSERT INTO #TransactionLog
VALUES (1, 1, 1, 2.00),
       (2, 2, 1, 2.40),
       (3, 3, 1, 1.80),
       (4, 1, 1, 2.00),
       (5, 1, 2, 2.00),
       (6, 2, 3, 2.40)

SELECT * from #Customer
SELECT * from #Product
SELECT * from #TransactionLog

SELECT
    c.CustomerName,
    p.ProductName,
    MIN(p.isActive)
FROM #TransactionLog t
join #Product p on t.ProductId = p.ProductId
join #Customer c on t.CustomerId = c.CustomerId
GROUP BY
    c.CustomerName,
    p.ProductName
HAVING
    MIN(p.isActive) = 1

DROP TABLE #Customer
DROP TABLE #Product
DROP TABLE #TransactionLog

I'm not sure why but when I try HAVING MIN(CAST(p.isActive AS INT)) = 1, I still get 2 rows of data associated with Bill. I'd like to eliminate Bill from the result.
Bill    Cheerios    1
Lisa    Cheerios    1
Bill    Corn Flakes 1
Fred    Corn Flakes 1

shawnt00 · Answer

with data as (
    SELECT
        c.CustomerName, p.ProductName,
        count(case when p.isActive = 0 then 1 end)
            over (partition by c.CustomerID) as countInactive 
    FROM #TransactionLog t
        inner join #Product  p on t.ProductId  = p.ProductId
        inner join #Customer c on t.CustomerId = c.CustomerId
)
select * from data where countInactive = 0;

You can accomplish it with min() over () as well if you prefer that logic.

Mark Schultheiss · Answer

I would propose a join instead:
CREATE TABLE #Customer (
    CustomerId int,
    CustomerName nvarchar(100),
    Address nvarchar(100),
)
INSERT INTO #Customer ( CustomerId, CustomerName, Address )
VALUES (1, 'Bill', '123 1st St'),
       (2, 'Fred', '111 Market St'),
       (3, 'Lisa', '01 Boulevard')

CREATE TABLE #Product (
    ProductId int,
    ProductName nvarchar(100),
    isActive bit
)
INSERT INTO #Product (ProductId, ProductName, isActive)
VALUES (1, 'Corn Flakes', 1),
       (2, 'Cheerios', 1),
       (3, 'Granoloa', 0)

CREATE TABLE #TransactionLog (
    LogId int,
    ProductId int,
    CustomerId int,
    Amount float
)
INSERT INTO #TransactionLog (LogId, ProductId ,CustomerId ,Amount )
VALUES (1, 1, 1, 2.00),
       (2, 2, 1, 2.40),
       (3, 3, 1, 1.80),
       (4, 1, 1, 2.00),
       (5, 1, 2, 2.00),
       (6, 2, 3, 2.40)

--SELECT * from #Customer
--SELECT * from #Product
--SELECT * from #TransactionLog

SELECT
    c.CustomerName,
    p.ProductName,
    p.isActive
FROM #TransactionLog t
INNER JOIN #Product p on t.ProductId = p.ProductId AND  p.isActive = 1
INNER JOIN #Customer c on t.CustomerId = c.CustomerId
GROUP BY
    c.CustomerName,
    p.ProductName

DROP TABLE #Customer
DROP TABLE #Product
DROP TABLE #TransactionLog

EDIT: PER NOTE, Get rid of Bill if he every ordered an inactive product
CREATE TABLE #Customer (
    CustomerId int,
    CustomerName nvarchar(100),
    Address nvarchar(100),
)
INSERT INTO #Customer ( CustomerId, CustomerName, Address )
VALUES (1, 'Bill', '123 1st St'),
       (2, 'Fred', '111 Market St'),
       (3, 'Lisa', '01 Boulevard')

CREATE TABLE #Product (
    ProductId int,
    ProductName nvarchar(100),
    isActive bit
)
INSERT INTO #Product (ProductId, ProductName, isActive)
VALUES (1, 'Corn Flakes', 1),
       (2, 'Cheerios', 1),
       (3, 'Granoloa', 0)

CREATE TABLE #TransactionLog (
    LogId int,
    ProductId int,
    CustomerId int,
    Amount float
)
INSERT INTO #TransactionLog (LogId, ProductId ,CustomerId ,Amount )
VALUES (1, 1, 1, 2.00),
       (2, 2, 1, 2.40),
       (3, 3, 1, 1.80),
       (4, 1, 1, 2.00),
       (5, 1, 2, 2.00),
       (6, 2, 3, 2.40)

--SELECT * from #Customer
--SELECT * from #Product
--SELECT * from #TransactionLog

SELECT
    c.CustomerName,
    p.ProductName,
    p.isActive
FROM #TransactionLog AS t
INNER JOIN #Product AS p on t.ProductId = p.ProductId   
    AND NOT EXISTS (
        SELECT 1 
        FROM #TransactionLog AS ts 
        INNER JOIN #Product AS ps 
            ON ts.ProductId = ps.ProductId 
                AND ps.isActive = 0 
        WHERE ts.CustomerId = t.CustomerId
    )
INNER JOIN #Customer c on t.CustomerId = c.CustomerId
GROUP BY
    c.CustomerName,
    p.ProductName,
    p.isActive

DROP TABLE #Customer
DROP TABLE #Product
DROP TABLE #TransactionLog

Icarus · Answer

This works for me - casting to INT as suggested in my comment:
CREATE TABLE #Customer (
    CustomerId int,
    CustomerName nvarchar(100),
    Address nvarchar(100),
)
INSERT INTO #Customer
VALUES (1, 'Bill', '123 1st St'),
       (2, 'Fred', '111 Market St'),
       (3, 'Lisa', '01 Boulevard')

CREATE TABLE #Product (
    ProductId int,
    ProductName nvarchar(100),
    isActive bit
)
INSERT INTO #Product
VALUES (1, 'Corn Flakes', 1),
       (2, 'Cheerios', 1),
       (3, 'Granoloa', 0)

CREATE TABLE #TransactionLog (
    LogId int,
    ProductId int,
    CustomerId int,
    Amount float
)
INSERT INTO #TransactionLog
VALUES (1, 1, 1, 2.00),
       (2, 2, 1, 2.40),
       (3, 3, 1, 1.80),
       (4, 1, 1, 2.00),
       (5, 1, 2, 2.00),
       (6, 2, 3, 2.40)

SELECT * from #Customer
SELECT * from #Product
SELECT * from #TransactionLog

SELECT
    c.CustomerName,
    p.ProductName,
    MIN(CAST(p.isActive AS int))
FROM #TransactionLog t
join #Product p on t.ProductId = p.ProductId
join #Customer c on t.CustomerId = c.CustomerId
GROUP BY
    c.CustomerName,
    p.ProductName
HAVING
    MIN(CAST(p.isActive AS INT)) = 1

DROP TABLE #Customer
DROP TABLE #Product
DROP TABLE #TransactionLog

Gordon Linoff · Answer

You can always convert to a number:
HAVING MIN(CONVERT(tinyint, p.isActive)) = 1

SQL Server : how do do HAVING criteria on bit value

4 Answers

Add your own answers!

Ask a Question