· 7 years ago · Sep 14, 2018, 08:44 AM
1Compare multiple columns, but only those having valid values, and create y/n flag if all are equal
2CREATE TABLE z_test
3(ID INT NOT NULL,
4D1 VARCHAR(8)NULL,
5D2 VARCHAR(8)NULL,
6D3 VARCHAR(8)NULL,
7D4 VARCHAR(8)NULL,
8DFLAG CHAR(1)NULL)
9
10INSERT INTO z_test VALUES (1,NULL,' ','000000','00000000',NULL)
11INSERT INTO z_test VALUES (1,'20120101','0000','20120101','00000000',NULL)
12INSERT INTO z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL)
13INSERT INTO z_test VALUES (2,'00000000','20090101','0','20090101',NULL)
14INSERT INTO z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL)
15INSERT INTO z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL)
16
17ID DFLAG
18---------------
191 N
201 Y
212 Y
222 Y
233 N
243 Y
25
26SELECT ID,
27 CASE
28 WHEN C = 1 THEN 'Y'
29 ELSE 'N'
30 END AS DFLAG
31FROM z_test
32 CROSS APPLY (SELECT COUNT(DISTINCT D) C
33 FROM (VALUES(D1),
34 (D2),
35 (D3),
36 (D4)) V(D)
37 WHERE LEN(D) > 0 /*Excludes blanks and NULLs*/
38 AND D LIKE '%[^0]%'/*Excludes ones with only zero*/) CA
39
40select case when
41 (D1 is null OR D2 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D2,'0',''))=0 OR D1=D2)
42AND (D1 is null OR D3 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D3,'0',''))=0 OR D1=D3)
43AND (D1 is null OR D4 is null OR LEN(REPLACE(D1,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D1=D4)
44AND (D2 is null OR D3 is null OR LEN(REPLACE(D2,'0',''))=0 OR LEN(REPLACE(D3,'0',''))=0 OR D2=D3)
45AND (D2 is null OR D4 is null OR LEN(REPLACE(D2,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D2=D4)
46AND (D3 is null OR D4 is null OR LEN(REPLACE(D3,'0',''))=0 OR LEN(REPLACE(D4,'0',''))=0 OR D3=D4)
47AND (LEN(REPLACE(D1,'0','')) > 0
48 OR LEN(REPLACE(D2,'0','')) > 0
49 OR LEN(REPLACE(D3,'0','')) > 0
50 OR LEN(REPLACE(D4,'0','')) > 0)
51THEN 'Y' ELSE 'N' END
52from z_test
53
54DECLARE @z_test table
55(ID INT NOT NULL,
56D1 VARCHAR(8)NULL,
57D2 VARCHAR(8)NULL,
58D3 VARCHAR(8)NULL,
59D4 VARCHAR(8)NULL,
60DFLAG CHAR(1)NULL)
61
62INSERT INTO @z_test VALUES (1,NULL,' ','000000','00000000',NULL)
63INSERT INTO @z_test VALUES (1,'20120101','0000','20120101','00000000',NULL)
64INSERT INTO @z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL)
65INSERT INTO @z_test VALUES (2,'00000000','20090101','0','20090101',NULL)
66INSERT INTO @z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL)
67INSERT INTO @z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL)
68
69;WITH Fixed AS
70(SELECT --converts columns with all zeros and any spaces to NULL
71 ID
72 ,NULLIF(NULLIF(D1,''),0) AS D1
73 ,NULLIF(NULLIF(D2,''),0) AS D2
74 ,NULLIF(NULLIF(D3,''),0) AS D3
75 ,NULLIF(NULLIF(D4,''),0) AS D4
76 FROM @z_test
77)
78SELECT --final result set
79 ID,
80 CASE
81 WHEN COALESCE(D1,D2,D3,D4) IS NULL THEN 'N' --all columns null
82 WHEN (D1 IS NULL OR D1=COALESCE(D1,D2,D3,D4)) --all columns either null or the same
83 AND (D2 IS NULL OR D2=COALESCE(D1,D2,D3,D4))
84 AND (D3 IS NULL OR D3=COALESCE(D1,D2,D3,D4))
85 AND (D4 IS NULL OR D4=COALESCE(D1,D2,D3,D4))
86 THEN 'Y'
87 ELSE 'N'
88 END
89 FROM Fixed
90
91ID
92----------- ----
931 N
941 Y
952 Y
962 Y
973 N
983 Y
99
100(6 row(s) affected)
101
102create function fnRowValid(@d1 varchar, @d2 varchar...)
103returns bit ------ 1 for true and 0 for false
104begin
105
106 declare table @validvalue (val varchar)
107
108 if isdate(d1) = 0 and isdate(d1) = 0 and ...
109 return 0
110
111 if isdate(@d1)
112 insert into @validvalue (val) values (@d1)
113 if isdate(@d2)
114 insert into @validvalue (val) values (@d2)
115 if isdate(@d3)
116 insert into @validvalue (val) values (@d3)
117 ...
118
119
120 if exists (select 1 from @validvalue)
121 if (select count(distinct val) from @validvalue) = 1
122 return 1
123
124 return 0
125
126end
127
128update z_test
129set dflag = dbo.fnRowValid(d1,d2,d3..)
130
131With RnkSource As
132 (
133 Select Id, D1, D2, D3, D4, DFlag
134 , Row_Number() Over ( Order By Id ) As RowNum
135 From z_test
136 )
137 , Source As
138 (
139 Select RowNum, Id, 'D1' As Col, D1 As Val
140 From RnkSource
141 Union All
142 Select RowNum, Id, 'D2', D2
143 From RnkSource
144 Union All
145 Select RowNum, Id, 'D3', D3
146 From RnkSource
147 Union All
148 Select RowNum, Id, 'D4', D4
149 From RnkSource
150 )
151Select Id
152 , Case When Count(*) = 4 Then 'Y' Else 'N' End As DFLAG
153From Source As S
154Where Len(Val) > 0
155Group By RowNum, Id