Sorting by a column when the name is derived via a CASE statement

Question

Sorting by a column when the name is derived via a CASE statement

j.clay 47557

SSC Eights!

Points: 835
More actions
October 15, 2018 at 6:17 am

#408965

Hi everyone
I have created the following SQL statement which works fine. However, it would definitely be beneficial if I could include [current age] in the WHERE part of the script.
If I simply add this into the WHERE clause (i.e. WHERE [current age] >20) then I get an error saying that [current age] isn't a valid column name.
Is there a way to include [current age] in the WHERE clause?
Many thanks
Jon

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

j.clay 47557 - Monday, October 15, 2018 6:17 AM
Hi everyone
I have created the following SQL statement which works fine. However, it would definitely be beneficial if I could include [current age] in the WHERE part of the script.
If I simply add this into the WHERE clause (i.e. WHERE [current age] >20) then I get an error saying that [current age] isn't a valid column name.
Is there a way to include [current age] in the WHERE clause?
Many thanks
Jon

Can we see your code?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 2

j.clay 47557 - Monday, October 15, 2018 6:17 AM
Hi everyone
I have created the following SQL statement which works fine. However, it would definitely be beneficial if I could include [current age] in the WHERE part of the script.
If I simply add this into the WHERE clause (i.e. WHERE [current age] >20) then I get an error saying that [current age] isn't a valid column name.
Is there a way to include [current age] in the WHERE clause?
Many thanks
Jon

You can put the CASE expression in a CROSS APPLY. then you can refer to the value "[current age]" in the sort or WHERE instead of having to paste in the entire case statement in the WHERE or ORDER BY clauses. If you paste a copy of your query into a reply, I can show you what I mean.

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 3

Thanks Jonathan 🙂

Here is my SQL:

select distinct cast(c.serialnumber AS INT), c.firstname, c.keyname, c.addressline1, c.addressline3, c.dateofbirth, c.donotmail, c.donotcontact, m.mailingtype,
GETDATE() AS [todays date],
DATEDIFF(YY,dateofbirth,GETDATE()) -
CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
ELSE 0
END AS [current age]

from contact c
inner join mailingpreference m on c.serialnumber=m.serialnumber
where m.mailingtype LIKE '%25%' and c.dateofbirth <'01/01/1993'
order by [current age] desc

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

j.clay 47557 - Monday, October 15, 2018 7:40 AM
Thanks Jonathan 🙂
Here is my SQL:
select distinct cast(c.serialnumber AS INT), c.firstname, c.keyname, c.addressline1, c.addressline3, c.dateofbirth, c.donotmail, c.donotcontact, m.mailingtype,
GETDATE() AS [todays date],
DATEDIFF(YY,dateofbirth,GETDATE()) -
CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
ELSE 0
END AS [current age]
from contact c
inner join mailingpreference m on c.serialnumber=m.serialnumber
where m.mailingtype LIKE '%25%' and c.dateofbirth <'01/01/1993'
order by [current age] desc

With the understanding that I don't know your data, the leading wildcard LIKE will likely be a performance issue.

That and the DISTINCT (which will also be a performance problem) both indicate a table design flaw.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 5

j.clay 47557 - Monday, October 15, 2018 7:40 AM
Thanks Jonathan 🙂
Here is my SQL:
select distinct cast(c.serialnumber AS INT), c.firstname, c.keyname, c.addressline1, c.addressline3, c.dateofbirth, c.donotmail, c.donotcontact, m.mailingtype,
GETDATE() AS [todays date],
DATEDIFF(YY,dateofbirth,GETDATE()) -
CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
ELSE 0
END AS [current age]
from contact c
inner join mailingpreference m on c.serialnumber=m.serialnumber
where m.mailingtype LIKE '%25%' and c.dateofbirth <'01/01/1993'
order by [current age] desc

select distinct cast(c.serialnumber AS INT), c.firstname, c.keyname, c.addressline1, c.addressline3, c.dateofbirth, c.donotmail, c.donotcontact, m.mailingtype, GETDATE() AS [todays date], x.[current age] from contact c inner join mailingpreference m on c.serialnumber=m.serialnumber CROSS APPLY(VALUES (DATEDIFF(YY,dateofbirth,GETDATE()) - CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1 ELSE 0 END)) x([current age]) where m.mailingtype LIKE '%25%' and c.dateofbirth <'01/01/1993' order by [current age] desc

j.clay 47557 SSC Eights! Points: 835 More actions · Answer 6

j.clay 47557

SSC Eights!

Points: 835

October 15, 2018 at 8:09 am

#2009618

Many thanks Jonathan - that works perfectly!

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 7

j.clay 47557 - Monday, October 15, 2018 8:09 AM
Many thanks Jonathan - that works perfectly!

Thanks j.clay,
I think you would be better putting a dates in format 'yyyymmdd' than '01/01/1993'. Different languages would get confused with that format.