SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/14/2012 :  09:19:39  Show Profile  Reply with Quote
Hi everyone!
what do you guys think of this query!

select count(*)  [Population],DG.GroupAge from (select ms.dob,case when datediff(YEAR,ms.dob,getdate())< 10 then 'G10'
when datediff(YEAR,ms.dob,getdate()) between 10 and 19 then 'G 10-20'
when datediff(YEAR,ms.dob,getdate())between 20 and 29 then 'G 20-30'
when datediff(YEAR,ms.dob,getdate())between 30 and 39 then 'G 30-40'
when datediff(YEAR,ms.dob,getdate())between 40 and 49 then 'G 40-50'
when datediff(YEAR,ms.dob,getdate())between 40 and 49 then 'G 50-60'
else 'G60+' end as GroupAge 
from people ms 
where ms.id in (select max(id) 
					 from dbo.table
					 group by customer_id) 
and ms.location in ('CO','TX')) as DG
Group by DG.GroupAge
Order by GroupAge


It runs fine! just want to see if any improvement I can add.

Thanks

--------------------------
Joins are what RDBMS's do for a living

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  09:59:19  Show Profile  Reply with Quote
Just couple of comments:

1. Your case expression for the G 50-60 does not seem to be correct. Did you mean "BETWEEN 50 and 59"?

2. Changing the IN clause to a table join may (or may not) help
....
               ELSE 'G60+' END AS GroupAge 
               FROM people ms 
               INNER JOIN 
               (
               		SELECT MAX(id) AS MaxId
				   FROM   dbo.table
				   GROUP BY
						  customer_id
               ) s ON s.MaxId = ms.id
           WHERE ms.location IN ('CO', 'TX')
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/14/2012 :  10:18:52  Show Profile  Reply with Quote
Hey sunitabeck!

Good catch :), Thanks a lot man!


--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2012 :  05:46:12  Show Profile  Reply with Quote
just a word of caution that datediff(YEAR,ms.dob,getdate()) may not give you the correct completed age always.

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

Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/15/2012 :  10:34:59  Show Profile  Reply with Quote
Oh, I really thought of this because it may sometimes round up/down!
Is this is right?

Please any alternative?

Thank you

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2012 :  10:40:43  Show Profile  Reply with Quote
quote:
Originally posted by xhostx

Oh, I really thought of this because it may sometimes round up/down!
Is this is right?

Please any alternative?

Thank you

--------------------------
Joins are what RDBMS's do for a living


it should something like below for precise age calculation

...
CASE WHEN MONTH(GETDATE()) < MONTH(ms.dob) 
       OR (MONTH(GETDATE())= MONTH(ms.dob)
       AND DAY(GETDATE()) < DAY(dob))
     THEN datediff(YEAR,ms.dob,getdate())-1
     ELSE datediff(YEAR,ms.dob,getdate())
END AS Age

...


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

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/15/2012 :  12:38:16  Show Profile  Reply with Quote
Another way to calculate age in years:
SELECT
(
   CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
   - 
   CAST(CONVERT(CHAR(8), ms.dob, 112) AS INT)
)/10000
This is from Itzik Ben-Gan's book "Inside Microsoft® SQL Server® 2008: T-SQL Querying"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000