473,320 Members | 1,909 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculating Age median from list of Dates of Birth

Hii friends

I need to calculate the age median from the given dates of birth in an employee database in MS SQl.

Please help...

Roma
Feb 12 '08 #1
5 7255
Delerna
1,134 Expert 1GB
do a datediff between the current date and their birth date
then take the average
for example in months it is
Expand|Select|Wrap|Line Numbers
  1. SELECT avg(datediff(m,getdate(),birthdate))
  2. FROM employees
  3.  
is that what you mean
Feb 12 '08 #2
deepuv04
227 Expert 100+
Hii friends

I need to calculate the age median from the given dates of birth in an employee database in MS SQl.

Please help...

Roma

Hi,


for calculating the age use the following sample query and change it according to your requirement


DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = '1983-07-04',@ToDate = '2008-07-04'

SELECT CASE WHEN @FromDate > @ToDate THEN NULL
WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
ELSE DATEDIFF(month, @FromDate, @ToDate)
END / 12.0


thanks
Feb 12 '08 #3
deepuv04
227 Expert 100+
do a datediff between the current date and their birth date
then take the average
for example in months it is
Expand|Select|Wrap|Line Numbers
  1. SELECT avg(datediff(m,getdate(),birthdate))
  2. FROM employees
  3.  
is that what you mean

Hi,

if the birth date is 2007-01-31 and today is 2008-02-01 and
in the code SELECT avg(datediff(m,getdate(),birthdate))
FROM employees the function datediff(m,getdate(),birthdate) will give you the difference as 1 month though it is differ by 1 day.
Feb 12 '08 #4
Delerna
1,134 Expert 1GB
Hi,

if the birth date is 2007-01-31 and today is 2008-02-01 and
in the code SELECT avg(datediff(m,getdate(),birthdate))
FROM employees the function datediff(m,getdate(),birthdate) will give you the difference as 1 month though it is differ by 1 day.

Then use days
avg(datediff(d,getdate(),birthdate))
Feb 12 '08 #5
Hey Thanks everyone for the code....
Feb 13 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Neil Grantham | last post by:
I am creating a database for Nursery children, and part of the reporting requirement, is to show those on the waiting list. Children will be elligible for entry by age, and so based on their...
4
by: Working_Girl | last post by:
Hi, I have a database with insurance clients and their dependents (spouses and children). We had a problem in the past with the twins and some of them have been entered with one month...
5
by: jonm4102 | last post by:
I'm trying to calculate the median of some numerical data. The data can only be found in a query (henceforth query 1) field I previously made, and I would prefer to calculate the median in a new...
8
by: helpless | last post by:
Access 2003 - I am trying to calculate the difference between a specific date 5/1/2007 (not in a data field) and other dates that ARE in a field called Birth Date. I am trying to have it fill into...
3
by: Emlyons | last post by:
Hello, I am completely new to Access. I am running a query but need to take the median value of observations in one of my tables. I've tried creating an expression but it is not working. I've also...
2
by: rn5a | last post by:
During registration, users have to provide their date of birth. For the date & month part, there are 2 dropdown lists & for the year, there's a textbox. These 3 fields are finally merged together...
10
by: Massimo30 | last post by:
Looking for answers and have done a search in books and websites and still can't find the answer: I am setting up a Market Trend DB on housing figures. I have the following fields in my table: My...
3
by: Hanoodah | last post by:
Good morning all, Please I need help in a form that I have designed to fill user information. This form needs two drop-down list about specific date, one about contract date, and the other about...
1
by: cmb3587 | last post by:
My code runs fine for the most part...the only time it fails is when I type in a negative to end the array. I don't want the negative number to be included in the array and I thought that is what...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.