asked on

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

Lee

Assuming activity.NumChildren is a varchar or nvarchar:

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

If it is numeric:

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

Jim Horn

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.

Lee

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

or

where isnull(activity.NumChildren, 0) <> 0

Lee Wadwell

Should your COUNT(CASE ...) be SUM(CASE ...)?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pposton

ASKER

Thanks! Great solution as it takes into consideration there might be non numeric characters in the field.