Help With Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help With Query

Author

Topic

rachish1915
Starting Member

3 Posts

Posted - 2012-06-05 : 13:32:57

I need the total of all in each age group. I don't know whether to use count or sum.

But first I need to get past this error: Msg 156, Level 15, State 1, Line 72
Incorrect syntax near the keyword 'FROM'.

Here's the code:

Declare @minage as int
set @minage = 0
Declare @maxage as int
set @maxage = 100

SELECT
m.OwnerOrganizationID as 'POL' ,

case
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 0 and 5
then ' Under 6'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 6 and 13
then '06 thru 13'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 14 and 17
then '14 thru 17'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 18 and 20
then '18 thru 20'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 21 and 29
then '21 thru 29'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 31 and 39
then '31 thru 39'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 41 and 49
then '41 thru 49'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 51 and 59
then '51 thru 59'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 61 and 69
then '61 thru 69'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 70 and 100
then '70 and Over'

END as 'Age Group',

COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 0 and 5
then ' Under 6'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 6 and 13
then '06 thru 13'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 14 and 17
then '14 thru 17'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 18 and 20
then '18 thru 20'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 21 and 29
then '21 thru 29'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 31 and 39
then '31 thru 39'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 41 and 49
then '41 thru 49'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 51 and 59
then '51 thru 59'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 61 and 69
then '61 thru 69'
else
COUNT(case when (datediff (year, convert (datetime, m.birthdate), getdate())) between 70 and 150
then '70 and Over'
END

FROM Members m
WHERE m.memberstatus ='Active' and
(datediff (year, convert (datetime, m.birthdate), getdate())) >=@minage and
(datediff (year, convert (datetime, m.birthdate), getdate())) <=@maxage

GROUP BY m.OwnerOrganizationid,
case
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 0 and 5
then ' Under 6'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 6 and 13
then '06 thru 13'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 14 and 17
then '14 thru 17'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 18 and 20
then '18 thru 20'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 21 and 29
then '21 thru 29'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 31 and 39
then '31 thru 39'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 41 and 49
then '41 thru 49'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 51 and 59
then '51 thru 59'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 61 and 69
then '61 thru 69'
when (datediff (year, convert (datetime, m.birthdate), getdate())) between 70 and 100
then '70 and Over'

ORDER BY m.OwnerOrganizationID asc, 'Age Group'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-05 : 14:09:07

You used a CASE expression in the counts and it's not necessary. Here's a fixed version, plus changed CASE expressions to be (potentially) better performing:

DECLARE @minage AS INT
SET @minage = 0
DECLARE @maxage AS INT
SET @maxage = 100 

SELECT 
m.OwnerOrganizationID AS 'POL' , 
CASE 
WHEN m.birthdate>DATEADD(YEAR,-6,GETDATE()) THEN ' Under 6'
WHEN m.birthdate>DATEADD(YEAR,-14,GETDATE()) THEN '06 thru 13'
WHEN m.birthdate>DATEADD(YEAR,-18,GETDATE()) THEN '14 thru 17'
WHEN m.birthdate>DATEADD(YEAR,-21,GETDATE()) THEN '18 thru 20'
WHEN m.birthdate>DATEADD(YEAR,-30,GETDATE()) THEN '21 thru 29'
WHEN m.birthdate>DATEADD(YEAR,-40,GETDATE()) THEN '30 thru 39'
WHEN m.birthdate>DATEADD(YEAR,-50,GETDATE()) THEN '40 thru 49'
WHEN m.birthdate>DATEADD(YEAR,-60,GETDATE()) THEN '50 thru 59'
WHEN m.birthdate>DATEADD(YEAR,-70,GETDATE()) THEN '60 thru 69'
ELSE '70 and Over' END AS [Age Group],
COUNT(*)
FROM Members m
WHERE m.memberstatus ='Active' AND 
m.birthdate<=DATEADD(YEAR, -@minage, GETDATE()) AND
m.birthdate>=DATEADD(YEAR, -@maxage, GETDATE())
GROUP BY m.OwnerOrganizationid,
CASE 
WHEN m.birthdate>DATEADD(YEAR,-6,GETDATE()) THEN ' Under 6'
WHEN m.birthdate>DATEADD(YEAR,-14,GETDATE()) THEN '06 thru 13'
WHEN m.birthdate>DATEADD(YEAR,-18,GETDATE()) THEN '14 thru 17'
WHEN m.birthdate>DATEADD(YEAR,-21,GETDATE()) THEN '18 thru 20'
WHEN m.birthdate>DATEADD(YEAR,-30,GETDATE()) THEN '21 thru 29'
WHEN m.birthdate>DATEADD(YEAR,-40,GETDATE()) THEN '30 thru 39'
WHEN m.birthdate>DATEADD(YEAR,-50,GETDATE()) THEN '40 thru 49'
WHEN m.birthdate>DATEADD(YEAR,-60,GETDATE()) THEN '50 thru 59'
WHEN m.birthdate>DATEADD(YEAR,-70,GETDATE()) THEN '60 thru 69'
ELSE '70 and Over' END
ORDER BY m.OwnerOrganizationID ASC, [Age Group]

I also fixed the headings on the 30 - 60 year old groups as they didn't specify 30, 40, 50 and 60 year olds.

rachish1915
Starting Member

3 Posts

Posted - 2012-06-05 : 14:42:16

I'll give it a try and let you know what happens.

rachish1915
Starting Member

3 Posts

Posted - 2012-06-05 : 15:21:15

It worked. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 16:18:17

It would be nice to create a Age group table with description and min max values to dispense with these CASE conditions. Then you just need a simple join with AgeGroup table to get associated age group for the age. This has the additional advantage of ability to merge/split or redining groups by just modifying data in table rather than changing code each time and modifying case conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources