[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 427

# Filter Query with Case Statement

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
pposton
• 2
• 2
1 Solution

Billing EngineerCommented:
>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 Commented:
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

Billing EngineerCommented:
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 Commented:
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

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.