Solved

# Filter Query with Case Statement

Posted on 2012-09-04
420 Views
I having a problem filtering a query which calculates the age of a client, groups the ages and then counts them based upon parameters.  I recieved help with the initial query (which works) and thought I could add the filter correctly....alas I could not.  The query is below with the EXISTS statement which currently has no effect on the results.  The results simply inculde all client records.

Any help would be appreciated it.  Thanks!

``````ALTER PROC [dbo].[demoAgeCount]

@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'

AS

SELECT age_group
, count(*) AS rows
FROM
(
SELECT
CASE
WHEN isdate(DOB) = 1 THEN
CASE

WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
< 19 THEN
'a. Less Than 19'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 19 AND 29 THEN
'b. 19-29'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 30 AND 39 THEN
'c. 30-39'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 40 AND 49 THEN
'd. 40-49'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 50 AND 59 THEN
'e. 50-59'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 60 AND 69 THEN
'f. 60-69'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 70 AND 79 THEN
'g. 70-79'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 80 AND 89 THEN
'h. 80-89'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
> 90 THEN
'i. Greater Than 90'
ELSE
NULL
END
else null end
as age_group

from client
) as T
where age_group is not null
AND
EXISTS (
SELECT DISTINCT activity.CharityCkID
FROM
activity join client on activity.charityckid=client.charityckid
WHERE
client.CharityCkID = activity.CharityCkID
AND activity.date >= @dStart
AND activity.date <= @dEnd)

group
by age_group
ORDER BY age_group
``````
0
Question by:pposton

LVL 142

Expert Comment

>The query is below with the EXISTS statement which currently has no effect on the results.
this is because the EXISTS() is not related with the other part of the query.

as long as the subquery in the EXISTS() does return at least 1 record, all the records from the other part will be returned.

I think you want to put it like this:

``````ALTER PROC [dbo].[demoAgeCount]

@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'

AS

SELECT age_group
, count(*) AS rows
FROM
(
SELECT
CASE
WHEN isdate(DOB) = 1 THEN
CASE

WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
< 19 THEN
'a. Less Than 19'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 19 AND 29 THEN
'b. 19-29'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 30 AND 39 THEN
'c. 30-39'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 40 AND 49 THEN
'd. 40-49'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 50 AND 59 THEN
'e. 50-59'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 60 AND 69 THEN
'f. 60-69'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 70 AND 79 THEN
'g. 70-79'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 80 AND 89 THEN
'h. 80-89'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
> 90 THEN
'i. Greater Than 90'
ELSE
NULL
END
else null end
as age_group

FROM
activity join client on activity.charityckid=client.charityckid
WHERE
client.CharityCkID = activity.CharityCkID
AND activity.date >= @dStart
AND activity.date <= @dEnd)

) as T
where age_group is not null

group
by age_group
ORDER BY age_group
``````
0

Author Comment

Will that return a distinct list though?  In the activity field there may be multiple occurances of the CharityCkID's between a date range.  I want to consider only the distinct ones within that date range.  That way I'm not counting individuals multiple times.
0

LVL 142

Accepted Solution

then we need to do like this:

``````ALTER PROC [dbo].[demoAgeCount]

@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'

AS

SELECT age_group
, count(*) AS rows
FROM
(
SELECT
CASE
WHEN isdate(DOB) = 1 THEN
CASE

WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
< 19 THEN
'a. Less Than 19'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 19 AND 29 THEN
'b. 19-29'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 30 AND 39 THEN
'c. 30-39'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 40 AND 49 THEN
'd. 40-49'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 50 AND 59 THEN
'e. 50-59'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 60 AND 69 THEN
'f. 60-69'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 70 AND 79 THEN
'g. 70-79'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
BETWEEN 80 AND 89 THEN
'h. 80-89'
WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
> 90 THEN
'i. Greater Than 90'
ELSE
NULL
END
else null end
as age_group

FROM client
WHERE EXISTS( SELECT NULL
FROM activity
WHERE activity.charityckid=client.charityckid
AND client.CharityCkID = activity.CharityCkID
AND activity.date >= @dStart
AND activity.date <= @dEnd
)

) as T
where age_group is not null

group
by age_group
ORDER BY age_group

``````
0

Author Closing Comment

That got it!!  Thanks!!

With every little bit of help, I keep learning a little bit more.  This is a great site thanks to great people like you angelIII.
0

## Featured Post

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…