August 31, 2011 at 8:13 am
Hello All,
See the sample data below.
I need to evaluate Field1, Field2, Field3, and Field4 and return a count of the number of non-
null records.
A=4
B=3
C=3
D=4...etc
I could write a CASE statement that would evaluate all possibilities (4x4 statements I believe) but I was hoping for something more extensible.
SELECT
ID,
Field1,
Field2,
Field3,
Field4
--,
--I need the case stement here
FROM
(
SELECT 'A' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'B' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,NULL AS Field4 UNION
SELECT 'C' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'D' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'E' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'F' AS ID,NULL AS Field1,NULL AS Field2,NULL AS Field3,4 AS Field4 UNION
SELECT 'G' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'H' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'I' AS ID,1 AS Field1,NULL AS Field2,NULL AS Field3,NULL AS Field4
)x
Thanks if you can help!
August 31, 2011 at 8:18 am
Sorry my desired results need revision.
A=4
B=2
C=3
D=4
E=4
F=1
G=3
H=4
I=1
August 31, 2011 at 8:24 am
Case when ISNULL(Field1,0) = 0 then 0 else 1 end +
Case when ISNULL(Field2,0) = 0 then 0 else 1 end +
Case when ISNULL(Field3,0) = 0 then 0 else 1 end +
Case when ISNULL(Field4,0) = 0 then 0 else 1 end as TotalNotNull
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
August 31, 2011 at 8:25 am
Something like this?
SELECT
ID,
COALESCE(Field1-Field1+1,0)+COALESCE(Field2-Field2+1,0)+COALESCE(Field3-Field3+1,0)+COALESCE(Field4-Field4+1,0)
FROM
(
SELECT 'A' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'B' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,NULL AS Field4 UNION
SELECT 'C' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'D' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'E' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'F' AS ID,NULL AS Field1,NULL AS Field2,NULL AS Field3,4 AS Field4 UNION
SELECT 'G' AS ID,NULL AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'H' AS ID,1 AS Field1,2 AS Field2,3 AS Field3,4 AS Field4 UNION
SELECT 'I' AS ID,1 AS Field1,NULL AS Field2,NULL AS Field3,NULL AS Field4
)x
John
August 31, 2011 at 8:40 am
Thank you both for the incredibly quick responses! Both look good.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply