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.
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'
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'
WHERE client.CharityCkID IN
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