Cluskitt
asked on
Getting birthdays within a date range
I had this query:
Now I need to get this for a range, defined by two variable dates. I also need it to return the age they would be. I've been able to get the results, but not the age, especially for cases when the dates cross years (for example, '31-10-2011' to '29-02-2012'). Also, I'm not sure how to handle ranges that span more than one year. For example, it would be desirable, given a range of '01-01-2010' to '01-01-2015' to see each person for each year, that is, something like:
NameA 01-05-2010 (25)
NameA 01-05-2011 (26)
...
NameA 01-05-2015 (29)
Alternatively, all dates being 01-05-1985 would be fine as well.
If this is too hard, just getting the first instance would be good.
SELECT psa_nome_abreviado + ' (' + CONVERT(varchar(10), psa_dt_nascimento, 103) + ')(' + CAST(YEAR(GETDATE())-YEAR(psa_dt_nascimento) +
CASE WHEN MONTH(psa_dt_nascimento)>MONTH(GETDATE()) OR (MONTH(psa_dt_nascimento)=MONTH(GETDATE()) AND DAY(psa_dt_nascimento)>DAY(GETDATE())) THEN 0 ELSE 1 END AS varchar(2)) + ')' AS 'Nome'
FROM uv_Inicio
WHERE cnt_arz_cod_accao NOT IN ('DMS','FCN')
AND MONTH(psa_dt_nascimento)=MONTH(GETDATE())
AND cnt_emp_empresa='41'
ORDER BY DATEADD(yy,YEAR(GETDATE())-YEAR(psa_dt_nascimento) + CASE WHEN MONTH(psa_dt_nascimento)<MONTH(GETDATE()) OR (MONTH(psa_dt_nascimento)=MONTH(GETDATE()) AND DAY(psa_dt_nascimento)<DAY(GETDATE())) THEN 1 ELSE 0 END,
psa_dt_nascimento)
This works fine for getting the birthdays of the current month and not just displaying, but also calculating how many years old said person would be.Now I need to get this for a range, defined by two variable dates. I also need it to return the age they would be. I've been able to get the results, but not the age, especially for cases when the dates cross years (for example, '31-10-2011' to '29-02-2012'). Also, I'm not sure how to handle ranges that span more than one year. For example, it would be desirable, given a range of '01-01-2010' to '01-01-2015' to see each person for each year, that is, something like:
NameA 01-05-2010 (25)
NameA 01-05-2011 (26)
...
NameA 01-05-2015 (29)
Alternatively, all dates being 01-05-1985 would be fine as well.
If this is too hard, just getting the first instance would be good.
ASKER
I can manage the where part. I have adapted the query found here: http://www.berezniker.com/content/pages/sql/microsoft-sql-server/birthday-query-ms-sql-server
I just don't know how to calculate the age, especially for those start in one year and end in another.
I just don't know how to calculate the age, especially for those start in one year and end in another.
what about datediff(month, getdate(), psa_dt_nascimento) / 12 ?
ASKER
That is based on the current date, not on the range inserted. The range could well be in the future or the past. The age has to be according to the age they would have at that point. I'm just not sure whether I should add from startdate, enddate, or some case check in between.
you can use @start_date or @end_date instead of getdate() ...
what would be the issue is if that is beyond a year range ...
let me see
what would be the issue is if that is beyond a year range ...
let me see
I use a function to calculate Age:
Then in General, your query would be
This handles date ranges over year boundaries.
A cleaner way of doing this would be with a calendar table.
CREATE FUNCTION [dbo].[GetAge] (@Birthdate datetime, @Now datetime)
RETURNS int AS
BEGIN
Declare @Age as int
if @Birthdate is null or @Now is Null
set @Age = 0
ELSE
BEGIN
set @Age = datepart(yyyy,@Now) - datepart(yyyy,@Birthdate)
IF datepart(m,@Now) < datepart(m,@Birthdate) OR ( datepart(m,@Now) = datepart(m,@Birthdate) AND datepart(d,@Now) < datepart(d,@Birthdate) )
SET @Age = @Age - 1
END
Return (@Age)
END
Then in General, your query would be
DECLARE @StartDate datetime = '2010-11-15'
DECLARE @EndDate datetime = '2011-02-12'
SELECT E.Name, E.Birthdate, , dbo.GetAge(e.ActiveBeginDate, CASE WHEN CAST(cast(datepart(yyyy,@StartDate)*10000 + datepart(month,e.ActiveBeginDate)*100 + datepart(d,e.ActiveBeginDate) as char(8)) as datetime) BETWEEN @StartDate AND @EndDate THEN CAST(cast(datepart(yyyy,@StartDate)*10000 + datepart(month,e.ActiveBeginDate)*100 + datepart(d,e.ActiveBeginDate) as char(8)) as datetime) ELSE CAST(cast(datepart(yyyy,@EndDate)*10000 + datepart(month,e.ActiveBeginDate)*100 + datepart(d,e.ActiveBeginDate) as char(8)) as datetime) END) AS Age
FROM MyTable e
WHERE CAST(cast(datepart(yyyy,@StartDate)*10000 + datepart(month,e.Birthdate)*100 + datepart(d,e.Birthdate) as char(8)) as datetime)
BETWEEN @StartDate AND @EndDate
OR
CAST(cast(datepart(yyyy,@EndDate)*10000 + datepart(month,e.Birthdate)*100 + datepart(d,e.Birthdate) as char(8)) as datetime)
BETWEEN @StartDate AND @EndDate
This handles date ranges over year boundaries.
A cleaner way of doing this would be with a calendar table.
ASKER
What do you mean by a calendar table? If it's a better way, then I'm all for it. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I'm trying to find out exactly what is the ActiveBeginDate field in your first query.
I'm also trying to adapt the calendar table in your link to our country, especially movable holidays. But in the meantime, I would like to get the first one working as well.
I'm also trying to adapt the calendar table in your link to our country, especially movable holidays. But in the meantime, I would like to get the first one working as well.
The ActiveBeginDate was from the table I was testing it on. Replace that with your Birthdate field.
ASKER
Ok, I almost got it. I just need one thing. How to use the ORDER BY clause with this? I used the calendar suggestion. It's easier and cleaner and it's working fine. But I'm not sure how to order them, especially when the range spans to another year.
If you are using the calendar table, you can order by the same field you are using in the BETWEEN Clause, c.CalendarDate if you want them to appear in the order they are going to happen (or did happen).
ASKER
That worked. Everything is great now, and I even got a new Calendar table which I've already used on other queries. Thanks a lot! ;)
replace:
AND MONTH(psa_dt_nascimento)=M
by something along these lines:
AND CONVERT(varchar(7), psa_dt_nascimento, 120) >= CONVERT(varchar(7), @start_date, 120)
AND CONVERT(varchar(7), psa_dt_nascimento, 120) <= CONVERT(varchar(7), @end_date, 120)