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

Question

I want to create a Y/N flag, where Y indicates every valid value in every column in a given row is equal, and N otherwise. I need to exclude from consideration any column that contains nulls, blanks, or all zeroes. Suppose:

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)

The desired output (excluding D1 through D4, though I don't want to drop them) is:

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

Speed is not a concern as this query will not be run very often but it is on a largish table.

Any pointers or suggestions would be very much 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

This 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 to sqlfiddle.

Answer 3

try this:

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)