SQL Server : SELECT and CASE

Question

I am trying to select data based on some parameters passed to my stored procedure. I have problems with the age, I am trying to do something like this:

If my stored procedure parameter @Age = 1 then I select age between 15 to 18, @Age = 2 then 19 - 25..., apparently this is incorrect, anyone can help. Thanks.:

SELECT
    User 
FROM
    [Member] m
WHERE 
    ((m.Gender = @Gender) or @Gender IS NULL)
    and ((DATEDIFF(hour,m.DOB,GETDATE())/8766) Between 
       CASE    
         WHEN @Age = 1 THEN (SELECT DATEDIFF(hour, m.DOB, GETDATE())/8766 WHERE (SELECT DATEDIFF(hour, m.DOB, GETDATE())/8766) between 15 and 18)
         WHEN @Age = 2 THEN (SELECT DATEDIFF(hour,m.DOB,GETDATE())/8766 WHERE (SELECT DATEDIFF(hour,m.DOB,GETDATE())/8766) between 19 and 25) 
       END)

A better way to deal with ages is usually to add the (negative) number of years required to todays date, and compare that to the `DOB` column directly. It avoids having to do weird fudges for leap years, and potentially allows indexes on the `DOB` column to be used — Damien_The_Unbeliever, Nov 18 '12 at 12:57

Laurence · Accepted Answer · 2012-11-18T12:40:27.167

4

I think this is what you are after (probably with some superfluous parenthesis):

Select 
  [User]
From
  [Member] m
Where (
    (m.Gender = @Gender) or 
    @Gender Is Null
  ) And (
    (@Age = 1 And DateDiff(hour, m.Dob, GetDate())/8766 Between 15 and 18) Or
    (@Age = 2 And DateDiff(hour, m.Dob, GetDate())/8766 Between 19 and 25) 
  )

If you've got a lot of clauses, it might be easier to read as (assuming a MemberID Primary Key)

Select
  [User]
From
  [Member] m
    Inner Join (
      Select
        MemberID,
        DateDiff(hour, m.Dob, GetDate())/8766 As Years
      From
        [Member]
    ) As y
    On m.MemberID = y.MemberID
Where (
    (m.Gender = @Gender) or 
    @Gender Is Null
  ) And (
    (@Age = 1 And y.Year Between 15 and 18) Or
    (@Age = 2 And y.Year Between 19 and 25) 
  )

Even better, you could add the ranges to a separate table called AgeRanges

+-------+------------+----------+
| AgeID | StartYears | EndYears |
+-------+------------+----------+
|     1 |         15 |       18 |
|     2 |         19 |       25 |
|   ... |        ... |      ... |
+-------+------------+----------+

Select
  [User]
From
  [Member] m
    Inner Join
  [AgeRanges] a
    On DateDiff(hour, m.Dob, GetDate())/8766 Between a.StartYears and a.EndYears And
       a.AgeID = @Age

You could also make DateDiff(hour, m.Dob, GetDate())/8766 a computed column on your members table to simplify things (and make indexing possible if performance became an issue).

edited Nov 18 '12 at 12:40

answered Nov 18 '12 at 12:30

Laurence

10,896
1
25
34

This. The ranges table makes things much simpler. Just wondering (@OP) - why use Hours/8766? DATEDIFF(year,date1,date2) should work. You'd have to experiment (If I had SQLServer running I'd do it in 20 secs), but I think it returns an integer rounded down, so that someone who's 1 day before their 19th birthday would be counted as 18. – sebt Nov 18 '12 at 12:49
@sebt - the issue with `DATEDIFF(year,...` is it counts the number of times that the year component of the date has changed between the two values. So it would report someone born on 31st December 2011 as being one year old on 1st January 2012. – Damien_The_Unbeliever Nov 18 '12 at 12:55
@Damien_The_Unbeliever: you're right: though I'm not at a machine I can test it on right now, I remember something like that. Best solution I can remember was starting from GETDATE, looping through DATEADD(year,-1,[this]) until we get beyond the birthdate, and counting the loops (being careful with +-1 errors). Trouble with DATEDIFF(hour,DOB,GETDATE)/8766 is that it averages out leap years to +6 hours every year rather than 1 day every 4 years (except.... can't even remember)! Rather leave leap-year stuff to the internal date-handling routines... – sebt Nov 18 '12 at 15:11
There's lots of articles on calculating an age properly. See http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate for example. – Laurence Nov 18 '12 at 15:28
@sebt - As I commented on the question, for these kind of range queries, I usually find it better to subtract the values from today's date and directly compare that to the DOB - no loops, and no fiddling with leap years. – Damien_The_Unbeliever Nov 18 '12 at 15:29

SQL Server : SELECT and CASE

1 Answers1