Hi,

I am using SQL Server 2008.

Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. 

How to retrieve only the failed rows which means not all zone are 1 or NULL.

In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item

Expected result:

Uniid <-> Stockcode <-> FailedRows

1670 <-> GJ-00064 <-> 1

--------------------------------------------

Please Advise.

Thank you.

Hello - The image is not visible to us, can you post Table Structure with Sample Data and the Output you are looking at ?

Please refer to the image.

Hello - Does this help:

-- Data Preparation
DECLARE @tblData TABLE( UnitID VARCHAR(10), StockCode VARCHAR(10), DocumentType INT,
Zone_1 SMALLINT, Zone_2 SMALLINT, Zone_3 SMALLINT )

INSERT INTO @tblData select 1670, 'GJ-00064', 1, 1,1,1
INSERT INTO @tblData select 1670, 'GJ-00064', 5, 1,NULL, NULL
INSERT INTO @tblData select 1670, 'GJ-00064', 6, NULL, NULL, NULL
INSERT INTO @tblData select 1670, 'GJ-00064', 7, 1, NULL, NULL

-- Final Selection
SELECT UnitID, StockCode, DocumentType FROM @tblData
WHERE ISNULL(Zone_1,0)+ISNULL(Zone_2,0)+ISNULL(Zone_3,0) = 0

Hope  this helps

Hi,

Thank you very much for your valuable support.

Zone_1/2/3 declared as bit data type.

Hi,

Even i cast the field as i am not getting expected result. I am getting all rows with NULL values.

Condition as follows...All columns in each row should not be 1 or NULL.

Hello - Please check if this works for you:

(I am assuming the condition as Failed Rows = Either all are 1 or all are Null )

-- Final Selection
SELECT * FROM
(
SELECT UnitID, StockCode, DocumentType,
CAST ( Zone_1 AS SMALLINT ) AS Zone_1, CAST ( Zone_2 AS SMALLINT ) AS Zone_2,
CAST ( Zone_3 AS SMALLINT ) AS Zone_3 FROM @tblData
)A
WHERE ISNULL(Zone_1,0)+ISNULL(Zone_2,0)+ISNULL(Zone_3,0) = 0
OR ISNULL(Zone_1,0)+ISNULL(Zone_2,0)+ISNULL(Zone_3,0) = 3

Hope this works for you !

Hi,

Thank you very much sir for your support. Below query helped me with little modification.

SELECT * FROM
(
SELECT UnitID, StockCode, DocumentType,
CAST ( Zone_1 AS SMALLINT ) AS Zone_1, CAST ( Zone_2 AS SMALLINT ) AS Zone_2,
CAST ( Zone_3 AS SMALLINT ) AS Zone_3 FROM @tblData
)A
WHERE ISNULL(Zone_1,0)+ISNULL(Zone_2,0)+ISNULL(Zone_3,0) > 0
OR ISNULL(Zone_1,0)+ISNULL(Zone_2,0)+ISNULL(Zone_3,0)< 2

Great to know that. Please do mark our responses as Answered in case you feel so.

This will help others to find answers to their questions  quickly