Compare multiple columns, but only those that have valid values, and create a y / n flag if all are equal

Question

I want to create a Y / N flag, where Y indicates that each valid value in each column in a given row is equal, and N otherwise. I need to exclude from consideration any column containing zeros, spaces, or all zeros. Let:

CREATE TABLE z_test (ID INT NOT NULL, D1 VARCHAR(8)NULL, D2 VARCHAR(8)NULL, D3 VARCHAR(8)NULL, D4 VARCHAR(8)NULL, DFLAG CHAR(1)NULL) INSERT INTO z_test VALUES (1,NULL,' ','000000','00000000',NULL) INSERT INTO z_test VALUES (1,'20120101','0000','20120101','00000000',NULL) INSERT INTO z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL) INSERT INTO z_test VALUES (2,'00000000','20090101','0','20090101',NULL) INSERT INTO z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL) INSERT INTO z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL) 

Desired result (excluding D1-D4, although I don't want to throw them):

 ID DFLAG --------------- 1 N 1 Y 2 Y 2 Y 3 N 3 Y 

The speed is not a concern, since this query will not be run very often, but it is in a rather large table.

Any pointers or suggestions would be greatly appreciated!

Answer 1

 SELECT ID, CASE WHEN C = 1 THEN 'Y' ELSE 'N' END AS DFLAG FROM z_test CROSS APPLY (SELECT COUNT(DISTINCT D) C FROM (VALUES(D1), (D2), (D3), (D4)) V(D) WHERE LEN(D) > 0 /*Excludes blanks and NULLs*/ AND D LIKE '%[^0]%'/*Excludes ones with only zero*/) CA 

Answer 2

It works:

 select case when (D1 is null OR D2 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D2,'0',''))=0 OR D1=D2) AND (D1 is null OR D3 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D3,'0',''))=0 OR D1=D3) AND (D1 is null OR D4 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D1=D4) AND (D2 is null OR D3 is null OR LEN(REPLACE(D2,'0',''))=0 OR LEN(REPLACE(D3,'0',''))=0 OR D2=D3) AND (D2 is null OR D4 is null OR LEN(REPLACE(D2,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D2=D4) AND (D3 is null OR D4 is null OR LEN(REPLACE(D3,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D3=D4) AND (LEN(REPLACE(D1,'0','')) > 0 OR LEN(REPLACE(D2,'0','')) > 0 OR LEN(REPLACE(D3,'0','')) > 0 OR LEN(REPLACE(D4,'0','')) > 0) THEN 'Y' ELSE 'N' END from z_test 

Here is the link in sqlfiddle.

Answer 3

try the following:

 DECLARE @z_test table (ID INT NOT NULL, D1 VARCHAR(8)NULL, D2 VARCHAR(8)NULL, D3 VARCHAR(8)NULL, D4 VARCHAR(8)NULL, DFLAG CHAR(1)NULL) INSERT INTO @z_test VALUES (1,NULL,' ','000000','00000000',NULL) INSERT INTO @z_test VALUES (1,'20120101','0000','20120101','00000000',NULL) INSERT INTO @z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL) INSERT INTO @z_test VALUES (2,'00000000','20090101','0','20090101',NULL) INSERT INTO @z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL) INSERT INTO @z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL) ;WITH Fixed AS (SELECT --converts columns with all zeros and any spaces to NULL ID ,NULLIF(NULLIF(D1,''),0) AS D1 ,NULLIF(NULLIF(D2,''),0) AS D2 ,NULLIF(NULLIF(D3,''),0) AS D3 ,NULLIF(NULLIF(D4,''),0) AS D4 FROM @z_test ) SELECT --final result set ID, CASE WHEN COALESCE(D1,D2,D3,D4) IS NULL THEN 'N' --all columns null WHEN (D1 IS NULL OR D1=COALESCE(D1,D2,D3,D4)) --all columns either null or the same AND (D2 IS NULL OR D2=COALESCE(D1,D2,D3,D4)) AND (D3 IS NULL OR D3=COALESCE(D1,D2,D3,D4)) AND (D4 IS NULL OR D4=COALESCE(D1,D2,D3,D4)) THEN 'Y' ELSE 'N' END FROM Fixed 

OUTPUT:

 ID ----------- ---- 1 N 1 Y 2 Y 2 Y 3 N 3 Y (6 row(s) affected) 

Answer 4

You can create a function for this.

 create function fnRowValid(@d1 varchar, @d2 varchar...) returns bit ------ 1 for true and 0 for false begin declare table @validvalue (val varchar) if isdate(d1) = 0 and isdate(d1) = 0 and ... return 0 if isdate(@d1) insert into @validvalue (val) values (@d1) if isdate(@d2) insert into @validvalue (val) values (@d2) if isdate(@d3) insert into @validvalue (val) values (@d3) ... if exists (select 1 from @validvalue) if (select count(distinct val) from @validvalue) = 1 return 1 return 0 end 

Then you can use this in the update statement ...

 update z_test set dflag = dbo.fnRowValid(d1,d2,d3..) 

Please do not flame me for this ... I have not tested this code explicitly. And this is the first solution that appeared in my head ... not very elegant, but should work. I'm not sure if you can use table variables in a function. Sorry for the ugly decision.

Answer 5

 With RnkSource As ( Select Id, D1, D2, D3, D4, DFlag , Row_Number() Over ( Order By Id ) As RowNum From z_test ) , Source As ( Select RowNum, Id, 'D1' As Col, D1 As Val From RnkSource Union All Select RowNum, Id, 'D2', D2 From RnkSource Union All Select RowNum, Id, 'D3', D3 From RnkSource Union All Select RowNum, Id, 'D4', D4 From RnkSource ) Select Id , Case When Count(*) = 4 Then 'Y' Else 'N' End As DFLAG From Source As S Where Len(Val) > 0 Group By RowNum, Id 

version of SQL Fiddle