troubleshooting Question

SQL SELECT SUM(CASE WHEN [ColumnName] IS NULL, or blank problem

Avatar of megnin
megninFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
4 Comments1 Solution2302 ViewsLast Modified:
I'm trying to use a query, part of which is below, to create some columns to use in an Excel pivot table report.
I started with just the [Yes] and [No] columns and then added the [NA], [Blank] and [oNULL] columns.

I get sums in the Yes, No and NA columns but the Blank and oNULL columns always give me the "ELSE" value equal to the number in the Yes column plus the number in the No column times whatever I have "ESLE" set to.  Normally it would be"...THEN 1 ELSE 0 END", but for the Blank and oNULL columns I change it to 10 and 100 so I could see how many times it was falling through to "ELSE".  

So, if there is a "1" in Yes, "2" in No and "4" in NA, then there will be a "30" in Blank and a "300" in oNULL using the values below.

The problem is, that's what it's doing instead of showing the number of actual blanks and NULL entries.

I just noticed that in my "AND (RegSelServ IN (@yes, @no) ) that since I've added the [NA] column I should add @NA to that IN clause.  I don't know how to resolve the blank and null issue though.

Any ideas?
DECLARE @OneStop varchar(50);
DECLARE @CaseManagerLName varchar(50);
DECLARE @FromDate smalldatetime;
DECLARE @ToDate smalldatetime;
DECLARE @ReviewerName varchar(50);
DECLARE @ReviewType varchar(50);
DECLARE @Yes VarChar(1);
DECLARE @No VarChar(1);
DECLARE @NA VarChar(1);
DECLARE @Blank VarChar(1)
SET @OneStop = '%';
SET @CaseManagerLName = '%';
SET @FromDate = '01/01/2000';
SET @ToDate = GetDate();
SET @ReviewerName = '%';
SET @ReviewType = '%';
SET @Yes = 'Y';
SET @No = 'N';
SET @NA = 'N/A';
SET @Blank = '';
 
SELECT OneStop, 'WIA_Adult' AS [Program]
  ,    LEFT(convert(char(10), ReviewDate, 127), 7) as ReviewDate
  ,    CaseManagerLName
  ,    ReviewerName
  ,   'RegSelServ' AS Question
  ,   SUM(CASE WHEN RegSelServ LIKE @Yes THEN 1 ELSE 0 END) AS Yes
  ,   SUM(CASE WHEN RegSelServ LIKE @No THEN 1 ELSE 0 END) AS No
  ,   SUM(CASE WHEN RegSelServ LIKE @NA THEN 1 ELSE 0 END) AS NA
  ,   SUM(CASE WHEN RegSelServ = @Blank THEN 1 ELSE 10 END) AS Blank
  ,   SUM(CASE WHEN RegSelServ IS NULL THEN 1 ELSE 100 END) AS oNULL
 
FROM   WIA_Adult
WHERE   (OneStop LIKE @OneStop)
  AND (CaseManagerLName LIKE @CaseManagerLName) 
  AND (ReviewDate BETWEEN @FromDate AND @ToDate) 
  AND (ReviewerName LIKE @ReviewerName) 
  AND (ReviewType LIKE @ReviewType) 
  AND (RegSelServ IN (@yes, @no) )
GROUP BY ReviewDate, CaseManagerLName, ReviewerName, OneStop
ASKER CERTIFIED SOLUTION
arturrrro

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros