• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Filtering a CASE statement

I had some help with the following code, but I forgot to mention that I needed to add some parameters and joins.  I thought I could figure that out, but evidently thought more of myself than I should!  

Anyway I put the procedure below but I need to add something like this:

FROM client inner join activity on client.CharityCkID=activity.CharityCkID
WHERE activity.username = @username AND activity.date >= @dstart AND activity.date <= @dEnd)

I need to include a client's income where they have at least one record in the activity table, but only consider them one time, basically based on a DISTINCT activity record between the  start date and end date.

Thanks in advanced!!

ALTER PROC [dbo].[demoIncome]
@username varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'



AS


SELECT CASE
		   WHEN income = 0 THEN
			   'a. No Income'
		   WHEN income > 0 AND income <= 200 THEN
			   'b. 0 to 200'
		   WHEN income > 200 AND income <= 500 THEN
			   'c. 201 to 500'
		   WHEN income > 500 AND income <= 1000 THEN
			   'd. 501 to 1000'
		   WHEN income > 1000 AND income <= 1500 THEN
			   'e. 1001 to 1500'
		   WHEN income > 1500 AND income <= 2000 THEN
			   'f. 1501 to 2000'
		   ELSE
			   'g. Greater than 2000'
	   END AS category
	 , count(*) AS [count]
FROM
	(SELECT cast(income AS NUMERIC) AS income
	 FROM
		 client
	 WHERE
		 isnumeric(income) = 1) a
GROUP BY
	CASE
		WHEN income = 0 THEN
			'a. No Income'
		WHEN income > 0 AND income <= 200 THEN
			'b. 0 to 200'
		WHEN income > 200 AND income <= 500 THEN
			'c. 201 to 500'
		WHEN income > 500 AND income <= 1000 THEN
			'd. 501 to 1000'
		WHEN income > 1000 AND income <= 1500 THEN
			'e. 1001 to 1500'
		WHEN income > 1500 AND income <= 2000 THEN
			'f. 1501 to 2000'
		ELSE
			'g. Greater than 2000'
	END
ORDER by category  

Open in new window

0
pposton
Asked:
pposton
  • 7
  • 5
1 Solution
 
lwadwellCommented:
So you probably want:
FROM client 
WHERE EXISTS (SELECT 1 FROM join activity 
              WHERE client.CharityCkID=activity.CharityCkID
                    AND activity.username = @username 
                    AND activity.date >= @dstart 
                    AND activity.date <= @dEnd)

Open in new window

instead of a join ... assuming that client is distinct.
0
 
lwadwellCommented:
and do not forget your existing predicate ...
FROM client 
WHERE isnumeric(income) = 1 AND
      EXISTS (SELECT 1 FROM join activity 
              WHERE client.CharityCkID=activity.CharityCkID
                    AND activity.username = @username 
                    AND activity.date >= @dstart 
                    AND activity.date <= @dEnd)

Open in new window

0
 
ppostonAuthor Commented:
Part of my problem (being self taught and new to this) I'm not really sure where to put that into the code, so a little more help there would be great.  I've tried it in a few places but everything produces an error.

Also, the client is distinct.  The CharityCkID connects the client table and the activity table.  They may have numerous records within the date parameters  in the activity table but I only pull the income information for the client once from the client table.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
lwadwellCommented:
Sorry ... I left the "FROM client" in snippet to indicate the location as that occurred only once in your original code.  Here is the full code:
ALTER PROC [dbo].[demoIncome]
    @username varchar(15),
    @dStart datetime = 'mm/dd/yyyy',
    @dEnd datetime ='mm/dd/yyyy'
AS
SELECT CASE
		   WHEN income = 0 THEN
			   'a. No Income'
		   WHEN income > 0 AND income <= 200 THEN
			   'b. 0 to 200'
		   WHEN income > 200 AND income <= 500 THEN
			   'c. 201 to 500'
		   WHEN income > 500 AND income <= 1000 THEN
			   'd. 501 to 1000'
		   WHEN income > 1000 AND income <= 1500 THEN
			   'e. 1001 to 1500'
		   WHEN income > 1500 AND income <= 2000 THEN
			   'f. 1501 to 2000'
		   ELSE
			   'g. Greater than 2000'
	   END AS category
	 , count(*) AS [count]
FROM
	(SELECT cast(income AS NUMERIC) AS income
	 FROM
		 client
	 WHERE
		 isnumeric(income) = 1 AND
            EXISTS (SELECT 1 FROM join activity 
                    WHERE client.CharityCkID=activity.CharityCkID
                          AND activity.username = @username 
                          AND activity.date >= @dstart 
                          AND activity.date <= @dEnd)) a
GROUP BY
	CASE
		WHEN income = 0 THEN
			'a. No Income'
		WHEN income > 0 AND income <= 200 THEN
			'b. 0 to 200'
		WHEN income > 200 AND income <= 500 THEN
			'c. 201 to 500'
		WHEN income > 500 AND income <= 1000 THEN
			'd. 501 to 1000'
		WHEN income > 1000 AND income <= 1500 THEN
			'e. 1001 to 1500'
		WHEN income > 1500 AND income <= 2000 THEN
			'f. 1501 to 2000'
		ELSE
			'g. Greater than 2000'
	END
ORDER by category  

Open in new window

0
 
lwadwellCommented:
The EXISTS is exactly that ... an existence check ... it runs the enclosed SQL and if at least 1 row is found ... it returns true, otherwise false.
0
 
ppostonAuthor Commented:
Your last response came in just after I uploaded mine.  That answered my question but it's giving me a few syntax errors.  The "and", "join" & "where" in your code and then just below in the "Order by" and "Group By".
0
 
lwadwellCommented:
remove the 'join' (a hang over that I missed when I edited your original code ... so sorry) ... i.e. the SQL inside the brackets should just be
SELECT 1 FROM activity 
WHERE client.CharityCkID=activity.CharityCkID
      AND activity.username = @username 
      AND activity.date >= @dstart 
      AND activity.date <= @dEnd

Open in new window

0
 
ppostonAuthor Commented:
Once again your post came right after I uploaded , that's awesome.  Anyway...the "join" and "where" still show a syntax error.
0
 
lwadwellCommented:
what 'join' ... there should be no 'join' anywhere in the SQL.
0
 
ppostonAuthor Commented:
I missed where you had said to remove the "join" origionally...I think when our posts where going so quickly.  Anyway I tried the code again and it runs without error but returns 0 results.  I verified that there is data to return through a simply query I've posted below.  Actualy it should return a little over 2000 records.

SELECT count (client.id)
FROM client join activity on activity.CharityCkID=client.CharityCkID
WHERE activity.username = 'caring' AND activity.date >='01/01/2009' and activity.date <='01/01/2010'
GROUP BY client.id
0
 
lwadwellCommented:
try this ...
SELECT count(*)
FROM client
WHERE isnumeric(income) = 1 
AND EXISTS (SELECT 1 FROM join activity 
            WHERE client.CharityCkID=activity.CharityCkID
                  AND activity.username = 'caring' 
                  AND activity.date >= '01/01/2010' 
                  AND activity.date <= '01/01/2009')

Open in new window


and

SELECT count (DISTINCT client.id)
FROM client join activity on activity.CharityCkID=client.CharityCkID
WHERE activity.username = 'caring' 
AND activity.date >='01/01/2009' 
and activity.date <='01/01/2010'
AND isnumeric(income) = 1 -- THIS IS IMPORTANT

Open in new window

0
 
ppostonAuthor Commented:
Got it.  My fault...I had made a slight change to the database yesterday that I had forgot about.  Once I took that into consideration your code works great!  Thanks so much for giving your time to help me out!!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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