Solved

Getting birthdays within a date range

Posted on 2011-09-20
13
509 Views
Last Modified: 2012-08-14
I had this query:
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)

Open in new window

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.
0
Comment
Question by:Cluskitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36567254
>Now I need to get this for a range, defined by two variable dates.

replace:
AND MONTH(psa_dt_nascimento)=MONTH(GETDATE())

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)

0
 
LVL 18

Author Comment

by:Cluskitt
ID: 36567318
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36567340
what about datediff(month, getdate(), psa_dt_nascimento) / 12 ?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 18

Author Comment

by:Cluskitt
ID: 36567392
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36567404
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
0
 
LVL 18

Expert Comment

by:lludden
ID: 36567513
I use a function to calculate Age:

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

Open in new window


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

Open in new window


This handles date ranges over year boundaries.

A cleaner way of doing this would be with a calendar table.

0
 
LVL 18

Author Comment

by:Cluskitt
ID: 36567539
What do you mean by a calendar table? If it's a better way, then I'm all for it. :)
0
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 36567620
Here is a good article on creating a calendar table: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

If you create the table in the link, your query becomes a bit cleaner (still using the getage function)

DECLARE @StartDate datetime = '2010-11-15'
DECLARE @EndDate datetime = '2011-02-12'

SELECT e.Name, e.Birthdate, dbo.GetAge(e.Birthdate,c.CalendarDate) AS Age
FROM Calendar c
INNER JOIN MyTable e ON DATEPART(m,e.Birthdate) = c.CalendarMonth AND datepart(d,e.Birthdate) = c.CalendarDay
WHERE c.CalendarDate BETWEEN @StartDate AND @EndDate

Open in new window


Calendar tables make all sorts of calculations with dates much easier and cleaner.\


0
 
LVL 18

Author Comment

by:Cluskitt
ID: 36572918
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.
0
 
LVL 18

Expert Comment

by:lludden
ID: 36573300
The ActiveBeginDate was from the table I was testing it on.  Replace that with your Birthdate field.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 36579481
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.
0
 
LVL 18

Expert Comment

by:lludden
ID: 36580235
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).
0
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 36580278
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! ;)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question