How do I use "between" in SQL Case ?

Question

0.00/5 (No votes)

See more:

, +

Hi everyone .
i want to select people by age depend on parametr @cat. By i want to output in when between clause , not value . How can i make this if I will have 3 clause or more? Maybe some universal way to add conditon to CASE clause?

SQL

SELECT COUNT (DISTINCT Table.users) FROM Table 
where

 CASE WHEN @cat = 1  THEN (Tabl.age between 18 and 20) and (Tabl.age between 40 and 50)
WHEN @cat = 2 THEN (Tabl.age between 21 and 42) and (Tabl.age between 40 and 70)
WHEN @cat = 3  THEN (Tabl.age between 57 and 68)  and (Tabl.age between 70 and 80)
END

Posted 29-Sep-15 3:30am

Андрей Голубцов

Updated 29-Sep-15 4:20am

v5

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Accepted Answer · 2015-09-29T03:43:00

Solution 1

You can use a tricky solution as follows-

SQL

SELECT COUNT (DISTINCT Table.users) FROM Table 
WHERE (@cat<>1 OR (Tabl.age between 18 and 20))
AND (@cat=1 OR (Tabl.age between 21 and 42))

For the first condition,
It will check if @cat not equals to 1 then skip the 2nd part of the expression and if @cat=1 it will use the filter as required.

For the second condition,
It will check if @cat=1 then skips the 2nd part of the expression and if @cat not equals to 1 then it will filter the records as required.

It's bit triky to understand how conditional OR works here but does the job.

Hope, it helps :)

In case of queries, please let me know.

Posted 29-Sep-15 3:43am

Suvendu Shekhar Giri

v2

Comments

Андрей Голубцов 29-Sep-15 9:54am

Yea , it works, but it will work only for 2 categories (@cat parametr) , but if my task will grow and i will have 3 value of parametr, it will not work . I edit my question, because i don't think at start, that quantity of parametr values will be important

Suvendu Shekhar Giri 29-Sep-15 10:16am

Agree. In that case, check @Mark's solution.

Maciej Los 29-Sep-15 12:16pm

5ed!

Suvendu Shekhar Giri 29-Sep-15 14:45pm

Thanks :)

Wendelius 29-Sep-15 13:15pm

Looks good also, a 5.

Suvendu Shekhar Giri 29-Sep-15 14:45pm

Thanks :)

Wendelius · Accepted Answer · 2015-09-29T03:47:00

You can't use case just like that but there are several ways to do the comparison. For example:

SQL

SELECT COUNT (DISTINCT Table.users) 
FROM Table 
WHERE Table.Age BETWEEN CASE @cat WHEN 1 THEN 18 ELSE 21 END
                AND     CASE @cat WHEN 1 THEN 20 ELSE 42 END;

ADDITION:

If multiple cases are required then the CASE list is just continued. For example (formatted a bit differently to emphasize the idea)

SQL

SELECT COUNT (DISTINCT Table.users)
FROM Table
WHERE Table.Age BETWEEN CASE @cat 
                           WHEN 1 THEN 1 
                           WHEN 2 THEN 11 
                           WHEN 3 THEN 21 
                           WHEN 4 THEN 31 
                           ELSE        41
                        END
                AND     CASE @cat 
                           WHEN 1 THEN 10
                           WHEN 2 THEN 20 
                           WHEN 3 THEN 30 
                           WHEN 4 THEN 40 
                           ELSE        999999999
                        END

Maciej Los · Accepted Answer · 2015-09-29T06:37:00

If you want to count users based on age category, you have to create helper-table to define age categories. Then you'll be able to join data from users table. Have a look at example:

SQL

DECLARE @users TABLE(UserId INT IDENTITY(1,1), UName VARCHAR(30), BirthDate DATE)

INSERT INTO @users(UName, BirthDate)
VALUES('A', '1995-02-02'), ('B', '1994-03-03'), ('C', '1993-04-04'),
('D', '1987-05-05'), ('G', '1986-06-06'), ('J', '1987-07-07'),
('E', '1985-08-08'), ('H', '1969-09-09'), ('K', '1968-10-10'),
('F', '1969-11-11'), ('I', '1959-12-12'), ('L', '1951-01-02')

DECLARE @AgeCat TABLE (AgeCatId INT IDENTITY(1,1), AgeFrom INT, AgeTo INT)

INSERT INTO @AgeCat (AgeFrom, AgeTo)
VALUES(18, 20), (21, 42), (43, 67)

DECLARE @curDate DATE = GETDATE()

SELECT t2.AgeCatId, t2.AgeFrom, t2.AgeTo, COUNT(t1.UserId) AS CountOfUsers
FROM (
    SELECT us.UserId, us.UName, DATEDIFF(YY,us.BirthDate, @curDate) AS Age
    FROM @users AS us
) AS t1 INNER JOIN @AgeCat AS t2 ON t1.Age BETWEEN t2.AgeFrom AND t2.AgeTo
--WHERE t2.AgeCatId = @catid
GROUP BY t2.AgeCatId, t2.AgeFrom, t2.AgeTo

Result:

AgeCatId	AgeFrom	AgeTo	CountOfUsers
1			18		20		1
2			21		42		6
3			43		67		5

Got it?

How do I use "between" in SQL Case ?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0