On 20 Jul 2004 07:59:29 -0700, Ellen Manning wrote:
Using SQL2000. I want to return the # of columns with non-null
values. Here's my query so far:
select
case when Dx1 is not null then 0 else 1 end +
case when Dx2 is not null then 0 else 1 end +
case when Dx3 is not null then 0 else 1 end +
case when Dx4 is not null then 0 else 1 end as DxCount
from tblClerkshipDataClean
where PalmName = @PalmName
There are 7 rows for the particular PalmName I'm using. The query
returns a result of 7. However, there are 25 values in Dx1 thru Dx4
so the query should be returning 25.
What am I doing wrong here? Thanks in advance.
First, some DDL to verify that I understand your setup:
create table tblClerkShipDataClean
( PalmName char(3) not null,
rowID int not null,
dx1 int null,
dx2 int null,
dx3 int null,
dx4 int null,
constraint pkey00 PRIMARY KEY (PalmName,rowID)
)
go
insert into tblClerkShipDataClean (PalmName,rowID,dx3,dx4) Values
('ABC',1,1,2)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx4) Values
('ABC',2,3,4,5)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx3,dx4) Values
('ABC',3,6,7,8,9)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx3,dx4) Values
('ABC',4,10,11,12,13)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx3,dx4) Values
('ABC',5,14,15,16,17)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx3,dx4) Values
('ABC',6,18,19,20,21)
insert into tblClerkShipDataClean (PalmName,rowID,dx1,dx2,dx3,dx4) Values
('ABC',7,22,23,24,25)
go
And now, to do it:
declare @PalmName char(3)
select @PalmName='ABC'
SELECT count(FLD) from (
SELECT Dx1 as FLD from tblClerkshipDataClean where PalmName = @PalmName
union all
SELECT Dx2 as FLD from tblClerkshipDataClean where PalmName = @PalmName
union all
SELECT Dx3 as FLD from tblClerkshipDataClean where PalmName = @PalmName
union all
SELECT Dx4 as FLD from tblClerkshipDataClean where PalmName = @PalmName
) T3
