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

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

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]

@username varchar(15),
@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.username = @username
			 AND activity.date >= @dStart
			 AND activity.date <= @dEnd) 

group
    by age_group 
ORDER BY age_group

Open in new window

0
pposton
Asked:
pposton
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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]

@username varchar(15),
@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.username = @username
			 AND activity.date >= @dStart
			 AND activity.date <= @dEnd) 

       ) as T
 where age_group is not null  

group
    by age_group 
ORDER BY age_group

Open in new window

0
 
ppostonAuthor 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then we need to do like this:

ALTER PROC [dbo].[demoAgeCount]

@username varchar(15),
@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.username = @username
			 AND activity.date >= @dStart
			 AND activity.date <= @dEnd
                   ) 

       ) as T
 where age_group is not null  

group
    by age_group 
ORDER BY age_group
                                            

Open in new window

0
 
ppostonAuthor 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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