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