WHERE clause not filtering correctly

I have a query which tries to calculate single mothers into groups.  It seems to work except for the last part of the WHERE statement which says "activity.NumChildren != '0' AND activity.NumChildren IS NOT NULL"  I've tried several different ways to accomplish but to no avail so far.  It always returns the same set of numbers.  The NumChildren field is varchar column.  The entire query is listed below.

Thanks for any help on figuring this out.

@username varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'

AS

SELECT 

COUNT(CASE WHEN maritalstatus = 'S'
THEN 1 ELSE 0 END) AS 'Single (S)',
COUNT(CASE WHEN maritalstatus = 'W'
THEN 1 ELSE 0 END) AS 'Widowed (W)',
COUNT(CASE WHEN maritalstatus = 'E'
THEN 1 ELSE 0 END) AS 'Separated (E)',
COUNT(CASE WHEN maritalstatus = 'D'
THEN 1 ELSE 0 END) AS 'Divorced (D)',
COUNT(CASE WHEN maritalstatus IS NULL
THEN 1 ELSE 0 END) AS 'Missing'

FROM client 
WHERE client.CharityCkID IN 
(SELECT activity.CharityCkID

FROM activity inner join client on client.CharityCkID=activity.CharityCkID

WHERE activity.username = @username AND activity.date >= @dStart AND activity.date <= @dEnd AND activity.NumChildren != '0' AND activity.NumChildren IS NOT NULL
)

Open in new window

ppostonPresident/OwnerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Since NumChildren is varchar, you could have ANYTHING in that column.

The only really safe way to do a numeric comparison on that column is a CASE statement; for example:

WHERE
    activity.username = @username AND
    activity.date >= @dStart AND
    activity.date <= @dEnd AND
    1 = CASE
        WHEN NumChildren IS NULL THEN 0
        WHEN NumChildren LIKE '%[^0-9]%' THEN 0 --if any nonnumeric char, exclude
        WHEN NumChildren > 0 THEN 1 --safe, since only digits appear in value
        ELSE 0 END
0
 
Lee SavidgeCommented:
Assuming activity.NumChildren is a varchar or nvarchar:

where isnull(activity.NumChildren, '0') != '0'

If it is numeric:

where isnull(activity.NumChildren, 0) != 0
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Two things:

(1)  Try WHERE COALESCE(activity.NumChildren, 0) <> 0
(2)  <probably not the solution, but anways...>  Looking at your WHERE clause you have a lot of AND's, without parenthesis, which means that every expression must eveluate to TRUE for a row to be returned.   You may want to consider parentheses to set order of precedence.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Lee SavidgeCommented:
Woops:
where isnull(activity.NumChildren, '0') <> '0'

or

where isnull(activity.NumChildren, 0) <> 0
0
 
lwadwellCommented:
Should your COUNT(CASE ...) be SUM(CASE ...)?
0
 
ppostonPresident/OwnerAuthor Commented:
Thanks!  Great solution as it takes into consideration there might be non numeric characters in the field.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.