Click here to Skip to main content
14,359,231 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have to list the names of the employees who fall within a given age range. I am new in learning to use variables/parameters in SQL, and I am having a hard time to get an output.

I don't know how to set a variable so a user can enter a age range and then get the names of the employees who fall within that range.


I am getting this error when I run this query

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 72


What I have tried:

Declare @AgeRange int
Set @AgeRange = DATEDIFF(YEAR, '1946-06-08', GETDATE()) 

WITH AgeData as 
  (
    SELECT FirstName, LastName,
    DateOfBirth, 
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS AGE
    FROM ptWMember as wm
    JOIN ptWorkingCertification as wc on wc.PK = wm.fkptWCertification
  ),
  GroupAge AS
(
  SELECT FirstName, LastName,
         DateOfBirth,
         Age,
         CASE
             WHEN @AgeRange < 30 THEN 'Under 30'
             WHEN @AgeRange BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN @AgeRange BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN @AgeRange > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
 )
SELECT FirstName, LastName
FROM AgeData
Posted
Updated 17-May-17 10:44am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Change this:
WITH AgeData as 

to:
;WITH AgeData as 

or finish previous line with [;]:
Set @AgeRange = DATEDIFF(YEAR, '1946-06-08', GETDATE()); 
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100