The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Find contacts with upcoming birthdays II

Find contacts with upcoming birthdays II

  • Comments 5

Note: Continuation from here.

METHOD 2: Using Filtered Views

Note: This method requires the Client side tools for SQL Server 2000 and/or SQL Server 2005 to be installed in order to execute the statements. Alternatively, the Filtered view statements can be used in Microsoft Excel with a Pivot table. Note that these statements can be used without adding custom fields to the Contact entity, but you may see much better performance if you add custom fields to the Contact entity to represent the Birthday_Month and Birthday_Day fields since those are going to be less expensive to search on with SQL Queries.

1. Connect to the OrganizationName_MSCRM database using SQL Server Query Analyzer or SQL Server Management Studio with Windows Authentication. (Start| Run | ISQLW will start SQL Query Analyzer. SQL Server Management Studio can be started by Start | Run | SQLWB.EXE.

2. For best performance add an index to the ContactBase.Birthdate field with the following query:

CREATE NONCLUSTERED INDEX [ndx_birthdate] ON [dbo].[ContactBase]

( [BirthDate] ASC

)WITH (FILLFACTOR=90)

ON [PRIMARY]

3. Run a query similar to the following queries:

Note that the queries below have used parameters to allow for easy substitution of how many days, weeks, or months into the future you want to search for Contacts with birthdays.

a. Get Contact birthdates FROM current date to 7 days FROM current date:

DECLARE @NextDays int

SELECT @NextDays = 7 -- Change this variable to see Birthdays greater than or equal to current date in days

SELECT firstname, lastname, fullname,

LEFT(DateAdd(dy,0,birthdate),11) AS Birthday

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate)=1 AND

datepart(dy,birthdate) > DatePart(dy,GetDate())

AND datepart(dy,birthdate) < DatePart(dy,DateAdd(dy,@NextDays,GetDate()))

ORDER BY DatePart(dy,BirthDate),FullName

------------------------------------------------------------------------

b. Get Contact birthdates in the next # of weeks:

DECLARE @NextNumberWeeks int

SELECT @NextNumberWeeks = 5 -- Change this variable to see Birthdays greater than or equal to current date in weeks

SELECT firstname, lastname, fullname,

LEFT(DateAdd(dy,0,birthdate),11) AS Birthday

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate)=1 AND

datepart(dy,birthdate) > DatePart(dy,GetDate())

AND datepart(wk,birthdate) < DatePart(wk,DateAdd(wk,@NextNumberWeeks,GetDate()))

ORDER BY DatePart(dy,BirthDate),FullName

-------------------------------------------------------------------------

c. Get Contact birthdates FROM current date to 1 or more months FROM current date:

DECLARE @NextMonths int

SELECT @NextMonths = 3 -- change this variable to show birthdays in the next # of months

SELECT firstname, lastname, fullname,

LEFT(DateAdd(dy,0,birthdate),11) AS Birthday

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate)=1 AND

datepart(dy,birthdate) > DatePart(dy,GetDate())

AND datepart(mm,birthdate) < DatePart(mm,DateAdd(mm,@NextMonths,GetDate()))

ORDER BY DatePart(dy,BirthDate),FullName

--------------------------------------------------------------------------

d. Get Contact birthdates FROM current date to 60 days FROM current date:

DECLARE @NextDays int

SELECT @NextDays = 60 -- Change this variable to see Birthdays greater than or equal to current date in days

SELECT Fullname, LEFT(DateAdd(dy,0,birthdate),11) AS Birthday

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate) = 1 AND

datediff

(

dd

,convert(datetime,'1900/'+cast(month(getdate()) AS varchar)+'/'+cast (day(getdate()) AS varchar),111)

,convert(datetime,'1900/'+cast(month(BirthDate) AS varchar)+'/'+cast (day(BirthDate) AS varchar),111)

) between 0 AND @NextDays

ORDER BY DatePart(dy,BirthDate),FullName

---------------------------------------------------------------------------

e. This query creates a hyperlink result that can be copied and pasted into Internet Explorer to open the CRM Server web site and to open the contact returned. This returns birthdates in the next Number of days.

--Get Contact birthdates FROM current date to 7 days FROM current date

DECLARE @NextDays int

SELECT @NextDays = 7 -- Change this variable to see Birthdays greater than or equal to current date in days

SELECT firstname, lastname, fullname,

LEFT(DateAdd(dy,0,birthdate),11) AS Birthday ,

'http://localhost:5555/sfa/conts/edit.aspx?id={' + Convert(Varchar(48),ContactId) + '}' AS Contact_Hyperlink -- this can be pasted into web browser to link to this contact

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate)=1 AND

datepart(dy,birthdate) > DatePart(dy,GetDate())

AND datepart(dy,birthdate) < DatePart(dy,DateAdd(dy,@NextDays,GetDate()))

ORDER BY DatePart(dy,BirthDate),FullName

f. Better query using Query from above with the query dates using the new CRM contact fields, new_birthday_day and new_birthday_month to show birthdays in the next month. The steps in Method 2 have to be followed for this query to work.

--Get Contact birthdates in next month from current date

DECLARE @NextMonth int,

@new_birthday_month int

SELECT @NextMonth = 1 -- Change this variable to see Birthdays in the next # months

SELECT @new_birthday_month = DatePart(mm,DateAdd(mm,@NextMonth,GetDate()))

SELECT firstname, lastname, fullname,

LEFT(DateAdd(dy,0,birthdate),11) AS Birthday ,

'http://localhost:5555/sfa/conts/edit.aspx?id={' + Convert(Varchar(48),ContactId) + '}' AS Contact_Hyperlink -- this can be pasted into web browser to link to this contact

FROM FilteredContact (NOLOCK)

WHERE new_birthday_month = @new_birthday_month

ORDER BY new_birthday_day,FullName

My next post I will discuss using Excel Dynamic Pivot Tables to accomplish this task. 

Chad Rexin

  • Hello!

    I think that code under section a. doesn't work properly; for example if todays date is 30th december, @NextDays = 7, person with birtdate on 3rd january will be left out. Or am i mistaken?

  • You are correct in that the script in section a does not work properly if the current date is within 6 days of the end of the year.  I'm close to having an update to the code that I'll post here once I've tested this scenario.  Thanks for bringing this to my attention.

    Chad Rexin

  • Chad, very useful scripts, thanks for posting.  I need to grab birthdays in the next 30 days, but as the previous post mentions, your script doesn't account for end of year scenarios.  In my case, the problem is with the order by.  The day of year would put the Jan birthdays before the Dec birthdays.  Thanks!

  • I think that code under section a. doesn't work properly; for example if todays date is 30th december, @NextDays = 7, person with birtdate on 3rd january will be left out.

  • It will work in all cases

    SELECT *  

     FROM TableName  

       WHERE CONVERT(datetime, CAST(MONTH(DateOFBirth) AS VARCHAR) +  

               '/' + CAST(DAY(DateOFBirth)AS VARCHAR) +  

               '/' + CASE WHEN MONTH(DateOFBirth) = 1 AND MONTH(GETDATE()) = 12 THEN CAST(YEAR(GETDATE()) + 1 AS VARCHAR) ELSE CAST(YEAR(GETDATE()) AS VARCHAR) END )  

           BETWEEN GETDATE() AND DATEADD(dd, 7, GETDATE())  

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post