Help with date calculation/convert function please!

[ausmoran]

I am attempting to query a database and have spent more time on this relatively simple query statement than I had planned. The table stores the BIRTH_DATE value as a CHAR data type, so I need to convert it to a SMALLDATETIME data type in order to perform any calculations on it. I am new to Transact SQL so am having a significant amount of trouble working through this. I'd appreciate any help you could throw my way!! Here's what I am trying to do....I am going to develop a series of SELECT statements which I will join via UNION statements that will show members who are '18 years of age and under'; '18-25 years of age'; '25-35 years of age'; etc etc etc. I am having a difficult time trying to figure out how to code the end of this statement that ascertains the number of members who fall into the age group. Here's what I have so far:

Declare @total Decimal (8,2)
------
SELECT CASE WHEN Member.Birth_Date = '00000000'
                        THEN 'Member Birthdate Unknown'
                         ELSE 'Under 18 years of age'
              END AS Age,
              COUNT(*) AS 'NumberOfMembers',
             '1' AS 'orderby',
              (CONVERT( decimal(8, 2), COUNT(*)) / @Total * 100) AS 'Percentage'
FROM dbo.Member_Association, dbo.Member -- unknown how you are joining these two tables...
WHERE (Status = 'A')
     AND (Primary_Indicator = 'P')
     AND( Member_Type_Code = 'R' OR Member_Type_Code = 'RA')
     AND( SELECT CONVERT( smalldatetime, Member.Birth_Date) ???????? <  DATEADD( year, - 1, GETDATE()) AND GETDATE()
   ate = '00000000')

 

[amrita418]

This should get you started:

create table #TableA (Id int, dob char(10))
insert into #TableA values (1, '20021003')
insert into #TableA values (1, '19901003')
insert into #TableA values (1, '19951003')

select    case when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '0-18 Group'
               when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '18-25 Group'
               else 'Unknown' end 'Groups',
          count(*) 'Total Members'
from      #TableA
group by  case when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '0-18 Group'
               when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '18-25 Group'
               else 'Unknown' end

OR

select    Groups, 
          count(*)
from      (select    case when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '0-18 Group'
                          when datediff(year, convert(datetime, dob), getdate()) between 0 and 18 then '18-25 Group'
                          else 'Unknown' end 'Groups'
           from      #TableA) b
group by  Groups

Regards,
AA

 

[ausmoran]

Thanks so very much (once again, AM RITA) for your help. I have a few questions, though. (1) if I use static dates such as '20021003', '19901003' or '19951003' then this code is only going to be valid today isn't it? Is there another way to set it up so I can use dynamic dates?

Also, (2) the code doesn't work as is. Not sure why...I changed DOB to Birth_Date (correct field name) but it is still failing. I get the following error in QA:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'Birth_date'.
[/color red]
I suspect that it has something to do with the absence of my correctly identifying the FROM table. Any ideas?

Thanks again!!!!
- Austin

 

[amrita418]

Can you post the code you currently have?

>> this code is only going to be valid today isn't it? Is there another way to set it up so I can use dynamic dates?
Also, I am not sure what you mean by this?

Regards,
AA

 

[rafrancisco]

Hi Austin,

Here's a link that provides a function in computing the age given a birth date and the current date:

http://www.sql-server-helper.com/functions/get-age.aspx

To use it in your query:

SELECT 
CASE WHEN [dbo].[ufn_GetAge] ( CAST([Birth_Date] AS 
              SMALLDATETIME), GETDATE() )
          BETWEEN 0 AND 18 THEN '0-18 Group'
     WHEN [dbo].[ufn_GetAge] ( CAST([Birth_Date] AS 
              SMALLDATETIME), GETDATE() )
          BETWEEN 19 AND 25 THEN '19-25 Group' END, COUNT(*)
FROM dbo.Member
GROUP BY 
CASE WHEN [dbo].[ufn_GetAge] ( CAST([Birth_Date] AS 
              SMALLDATETIME), GETDATE() )
          BETWEEN 0 AND 18 THEN '0-18 Group'
     WHEN [dbo].[ufn_GetAge] ( CAST([Birth_Date] AS 
              SMALLDATETIME), GETDATE() )
          BETWEEN 19 AND 25 THEN '19-25 Group' END

Hope this helps.