Ages and Age Ranges
-
Hi There,
I am trying to get the following syntax to pull me back a column in my result set that allows me to work out which "Age Category" they fall under.
For example if the age is between 18 and 24 the range would be 18-24 and so on.
When I run the below query I get the following error and am unsure why -
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'Range'
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS Age,
(SELECT CASE WHEN Age BETWEEN 18 AND 24 THEN '18-24'
ELSE 'Not In Age Range'
END) AS Age Range
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROMdbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ONCOMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PERSON
ONORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERECOMM.[comm-type] = 'WB'
ANDORIGIN.[PERSON-TYPE] = 'O'
ORDER BY COMM.[crt-date]
No idea where I'm going wrong. Your help will be greatly appreciated.
Thanks
-
Change
Age Range
to
[Age Range]
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Ryan Keast (12/9/2009)
Hi There,I am trying to get the following syntax to pull me back a column in my result set that allows me to work out which "Age Category" they fall under.
For example if the age is between 18 and 24 the range would be 18-24 and so on.
When I run the below query I get the following error and am unsure why -
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'Range'
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS Age,
(SELECT CASE WHEN Age BETWEEN 18 AND 24 THEN '18-24'
ELSE 'Not In Age Range'
END) AS Age Range
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROMdbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ONCOMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PERSON
ONORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERECOMM.[comm-type] = 'WB'
ANDORIGIN.[PERSON-TYPE] = 'O'
ORDER BY COMM.[crt-date]
No idea where I'm going wrong. Your help will be greatly appreciated.
Thanks
Your problem is here:
...
END) AS Age Range
...
You have a space in the column alias. You nned to do this: [Age Range] or AgeRange.
I prefer the later as I don't like spaces in column names.
-
The new column [Age range] has the new column [Age] as an input. It can't. You can either replace references to Age with the entire expression, or use an "onion" select as follows:
SELECT [Communication Reference],[Created Date],
[Main Code],
[Sub Code],
[Current Officer Code],
[PERSON-TITLE],
[FORENAMES],
[SURNAME],
[D-O-B],
[Age],
[Age Range] = CASE WHEN [Age] BETWEEN 18 AND 24 THEN '18-24' ELSE 'Not In Age Range' END
[GENDER],
[Place Reference],
[Person Reference]
FROM (
SELECT COMM.[comm-reference] AS 'Communication Reference',
COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS Age,
--(SELECT CASE WHEN Age BETWEEN 18 AND 24 THEN '18-24' ELSE 'Not In Age Range' END) AS Age Range
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROM dbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ON COMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN dbo.[CORE_CO-PERSON] AS PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE COMM.[comm-type] = 'WB'
AND ORIGIN.[PERSON-TYPE] = 'O'
) d
There's not much in it really. I like to use the onion select because it can make an otherwise impossibly complex query, readable to an old git like me.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS [Age],
(SELECT CASE WHEN [Age] BETWEEN 18 AND 24 THEN '18-24'
ELSE 'Not In Age Range'
END) AS Age_Range,
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROMdbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ONCOMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PERSON
ONORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERECOMM.[comm-type] = 'WB'
ANDORIGIN.[PERSON-TYPE] = 'O'
ORDER BY COMM.[crt-date]
Now getting the following message -
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Age'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Age'.
Again probably something really obvious.
Thanks for yor help so far
-
I have noticed several other issues. What would help is if you could provide the DDL for the tables (CREATE TABLE statements), sample data for the tables in a readily consummable format (we should be able to copy/paste/run in SSMS or QA), expected results based on the sample data.
One thing I noticed was the use of SELECT in what appears to be subqueries, but no FROM clause.
-
Ryan Keast (12/9/2009)
[/code]Now getting the following message -
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Age'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Age'.
Again probably something really obvious.
Thanks for yor help so far
Yes - it's defined in the SELECT.
Use an "onion select" or replace references to [Age] in your SELECT with the entire expression from which it's derived, like this:
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS [Age],
(SELECT CASE WHEN (SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) BETWEEN 18 AND 24 THEN '18-24'
ELSE 'Not In Age Range'
END) AS Age_Range,
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROM dbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ON COMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE COMM.[comm-type] = 'WB'
AND ORIGIN.[PERSON-TYPE] = 'O'
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Perfect. Thanks so much
-
You're welcome.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
How would I incorporate multiple age ranges?
So take it a bit further and say if between age 25 and 48 then "25-48" and add more if I need too?
Thanks
-
Hi there,
I am trying to write some code that will let me pull back multiple age ranges, if an age falls into a particular bracket.
What I've got is below - but it is erroring with the following message -
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'BETWEEN'.
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.[FORENAMES],
PERSON.[SURNAME],
PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) AS [Age],
(SELECT CASE WHEN (SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B])
THEN 0
ELSE 1 END) BETWEEN 18 AND 24 THEN '18-24'
ELSE BETWEEN 25 AND 41 THEN '25-41'
ELSE 'Not In Age Range'
END) AS Age_Range,
PERSON.[GENDER],
ORIGIN.[place-ref] AS 'Place Reference',
ORIGIN.[PERSON-REF]AS 'Person Reference'
FROM dbo.[IH_EM-COMMUNICATION] AS COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] AS ORIGIN
ON COMM.[comm-reference] = ORIGIN.[comm-reference]
INNER JOIN
dbo.[CORE_CO-PERSON] AS PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE COMM.[comm-type] = 'WB'
AND ORIGIN.[PERSON-TYPE] = 'O'
Please advise what I am doing wrong. Thanks
-
This expression for returning age range bears little resemblance to TSQL:
(SELECT CASE WHEN (SELECT DATEDIFF(year, PERSON.[D-O-B], GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B]) THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B]) THEN 0
ELSE 1 END) BETWEEN 18 AND 24 THEN '18-24'
ELSE BETWEEN 25 AND 41 THEN '25-41'
ELSE 'Not In Age Range'
END)
AS Age_Range,
Read the section in Books Online about simple and searched case statements.
Here's a little test scenario which will help:
DECLARE @PERSOND_O_B DATETIMESET @PERSOND_O_B = '1979-12-15 13:27:42.997'
SELECT Age_Range =
CASE
WHEN (DATEDIFF(year, @PERSOND_O_B, GETDATE())
- CASE
WHEN MONTH(GETDATE()) > MONTH(@PERSOND_O_B) THEN 0
WHEN MONTH(GETDATE()) = MONTH(@PERSOND_O_B) AND DAY(GETDATE()) >= DAY(@PERSOND_O_B) THEN 0
ELSE 1 END
) BETWEEN 18 AND 24 THEN '18-24'
WHEN (27) BETWEEN 25 AND 41 THEN '25-41'
ELSE 'Not In Age Range'
END
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Ryan Keast (12/9/2009)
How would I incorporate multiple age ranges?So take it a bit further and say if between age 25 and 48 then "25-48" and add more if I need too?
Thanks
Use the "Onion Select" ((c) J Moden) described to you in previous posts on this thread.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hello again,
Thanks for your response.
But due to me being almost a novice when it comes to TSQL I am really strugging with the multiple age ranges and the "onion select" that has been recommended to me.
I now have the below code
SELECT COMM.[comm-reference] AS 'Communication Reference',COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE], PERSON.FORENAMES,
PERSON.SURNAME, PERSON.[D-O-B],
(SELECT DATEDIFF(year, PERSON.[D-O-B],
GETDATE()) - CASE WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B])
THEN 0 WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B])
AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B]) THEN 0 ELSE 1 END)
AS Age,
PERSON.GENDER,
PERSON.[ORIGIN-CODE],
ORIGIN.[PLACE-REF] AS 'Place Reference',
ORIGIN.[PERSON-REF] AS 'Person Reference',
CAST(CASE
WHEN EXTENSION.[POTENTIALLY-VULNERABLE]
IS NULL THEN 'No' ELSE 'Yes' END AS NVARCHAR(5))
AS Vulnerable
FROM dbo.[IH_EM-COMMUNICATION] COMM
INNER JOIN
dbo.[IH_EM-ORIGINATOR] ORIGIN
ON COMM.[comm-reference] = ORIGIN.[COMM-REFERENCE]
INNER JOIN
dbo.[CORE_CO-PERSON] PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
LEFT OUTER JOIN
dbo.[CORE_CO-PERSON-EXTENSION] EXTENSION
ON EXTENSION.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE (COMM.[comm-type] = 'WB')
AND (ORIGIN.[PERSON-TYPE] = 'O')
ORDER BY COMM.[crt-date]
What I want to achieve is create another field Age Range.
If Age is a particular values then pull back a particular age range.
So If >=18 or <=24 PULL BACK "18 - 24"
So If <=25 of >=49 PULL BACK "25 - 49"
and so on.
Sorry that I don't understand what you have already posted.
Thanks
Ryan
-
Try this, Ryan:
SELECT *,[Age Range] = CASE
WHEN [Age] < 18 THEN 'Juvenile'
WHEN [Age] BETWEEN 18 AND 31 THEN '18 TO 31'
WHEN [Age] BETWEEN 32 AND 41 THEN '32 TO 41'
WHEN [Age] BETWEEN 42 AND 51 THEN '42 TO 51'
ELSE 'Stumbly crumbly' END
FROM (
SELECT
COMM.[comm-reference] AS 'Communication Reference',
COMM.[crt-date] AS 'Created Date',
COMM.[main-code] AS 'Main Code',
COMM.[sub-code] AS 'Sub Code',
COMM.[current-officer-code] AS 'Current Officer Code',
PERSON.[PERSON-TITLE],
PERSON.FORENAMES,
PERSON.SURNAME,
PERSON.[D-O-B],
[Age] = DATEDIFF(year, PERSON.[D-O-B],
GETDATE()) - CASE
WHEN MONTH(GETDATE()) > MONTH(PERSON.[D-O-B]) THEN 0
WHEN MONTH(GETDATE()) = MONTH(PERSON.[D-O-B]) AND DAY(GETDATE()) >= DAY(PERSON.[D-O-B]) THEN 0
ELSE 1 END,
PERSON.GENDER,
PERSON.[ORIGIN-CODE],
ORIGIN.[PLACE-REF] AS 'Place Reference',
ORIGIN.[PERSON-REF] AS 'Person Reference',
CAST(
CASE WHEN EXTENSION.[POTENTIALLY-VULNERABLE] IS NULL THEN 'No' ELSE 'Yes' END
AS NVARCHAR(5)) AS Vulnerable
FROM dbo.[IH_EM-COMMUNICATION] COMM
INNER JOIN dbo.[IH_EM-ORIGINATOR] ORIGIN
ON COMM.[comm-reference] = ORIGIN.[COMM-REFERENCE]
INNER JOIN dbo.[CORE_CO-PERSON] PERSON
ON ORIGIN.[PERSON-REF] = PERSON.[PERSON-REF]
LEFT OUTER JOIN dbo.[CORE_CO-PERSON-EXTENSION] EXTENSION
ON EXTENSION.[PERSON-REF] = PERSON.[PERSON-REF]
WHERE (COMM.[comm-type] = 'WB')
AND (ORIGIN.[PERSON-TYPE] = 'O')
) d
ORDER BY [Created Date]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply