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

Find contacts with upcoming birthdays - Redux

Find contacts with upcoming birthdays - Redux

  • Comments 1

You may remember the blog posts on retrieving a list of contacts with upcoming birthdays in Microsoft CRM. There were some comments, questions, and a flaw in at least one of the SQL Scripts used to pull birthdays where it would not work in all scenarios. This post will address some of those questions.

Original Birthdate blog posts with Microsoft CRM.

http://blogs.msdn.com/crm/archive/2007/05/09/find-contacts-with-upcoming-birthdays.aspx

http://blogs.msdn.com/crm/archive/2007/05/14/find-contacts-with-upcoming-birthdays-ii.aspx

http://blogs.msdn.com/crm/archive/2007/05/17/find-contacts-with-upcoming-birthdays-iii.aspx

Specifically the blog post, http://blogs.msdn.com/crm/archive/2007/05/14/find-contacts-with-upcoming-birthdays-ii.aspx, contains a SQL Script similar to this.

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

This script does not work when the following scenario occurs: If today’s date is December 30th, @NextDays = 7, contacts with birthdates on January 3rd will be left out. Thanks goes out to the community user, Jernej, for pointing this scenario out. The problem is that the script does not properly handle finding the contacts with birthdates in the next X days if that takes them into a new year. The script below was created to address this scenario and was also enhanced to allow for easier usability and testing by creating parameters for the Next x days and the Current date. This allows an administrator to test and verify that this pulls all pertinent records depending on what values are substituted for the @NextDays and @CurrentDate variables.

The script is commented to describe what is happening and I’ve also included some sample indexes that can be used if they don’t already exist in your Microsoft CRM Database. Note that after adding birthdate indexes that the Logical reads against the ContactBase table dropped from 50223 to 8556 and reduced the scan count from 3 to 2 in one of the larger test databases I ran this against. This also reduced the execution time in SQL from ~8 seconds to just over 1 second. This can help greatly reduce the load on the SQL Server if this SQL script or one similar to it is put into a report or SDK application code that is frequently accessed by your Microsoft CRM Users.

I’ve also seen some comments regarding automating this so that an email is sent out automatically when it is a contact’s birthdate or a few days before their birthday. This could be done by a variety of different methods, some of which may require some additional work after they are performed to finish sending the email. I won’t go into details on those methods as that is more involved, but may be something I expand on in a future post.

a. You could put this SQL code in a Workflow assembly and execute a workflow rule to get the emails sent out.

b. You can put this code or similar code into a SQL SRS Report and have it scheduled to run once a week or some other given time and send out emails to users.

Hopefully this has been helpful in helping you customize and use Microsoft CRM to it’s fullest capabilities that can meet your company’s business needs and requirements.

/*

TITLE: SQL Script to Find Contacts with Birthdates in next X days.

DATE: 8-20-2007

NOTES:

1. This SQL Query could be run from SQL Server Management Studio (SQL Server 2005), or SQL Server Query Analyzer (SQL 2000), but is going to be most useful if included in a Microsoft CRM SDK application or as part of a Microsoft CRM SRS Report. It could also be used as a query with a Dynamic Excel report

2. Parameters can be changed to test query used. Primary query can be pulled from here and used in Microsoft CRM SDK applications or in a Microsoft CRM SRS report.

@NextDays is the number of days that the Contacts with birthdays are to be returned i.e., pull all contacts with birthdays 14 days from now.

@CurrentDate is the date you want to start from (typically this would be GetDate() or the currentdate, but can be specified here to test the SQL Query. Leap year birthdates should be returned.

a. Limited testing was done, but should work in the majority of scenarios for getting birthdates in the next X Days. Please verify query works for your specific needs before implementing in production.

3. Data is selected into a Temp table, ##Contact to limit blocking and to reduce the load on SQL by giving it a smaller dataset to work with for the final queries. Also, a NOLOCK hint is used when selecting the data out for the Contact view to prevent database blocking while this query executes.

4. If an index doesn't already exist on the birthdate field, you may want to add one to improve performance of this query.

5. This doesn't take into effect that all dates are stored in the MSCRM database in the UTC datetime format. This could affect a small number of records if the difference between local time and UTC time was more than a few hours and stores the date as a different day (either one day earlier or one day later) than the actual birthdate in local time.

6. This returns columns needed to contact these contacts, but if you don't need those columns, remove them from the select statements to lighten the load on the SQL Server. (If you do not need any address data, removing the address columns could potentially eliminate thousands of SQL Logical reads from needing to occur depending on the size of the dataset.)

7. Sample performance statistics (note that these do not include the address columns). Also note that the logical reads and execution times will vary depending on the hardware used for the SQL Server. This is primarily used to show the significant increase in performance that can be had by simply adding an index to the ContactBase.Birthdate field when using a query similar to the one in this sample.

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

Before ContactBase.Birthdate index added -- 563523 contacts, 9,045 had birthdates

Table 'ContactBase'. Scan count 3, logical reads 50223, physical reads 0, read-ahead reads 50223, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 437 ms, elapsed time = 8195 ms.

(9045 row(s) affected)

-- after birthdate index -- 563523 contacts, 9,045 had birthdates

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ContactBase'. Scan count 2, logical reads 8556, physical reads 0, read-ahead reads 7769, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(9045 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 313 ms, elapsed time = 1283 ms.

8. Sample Birthdate indexes for increased performance using Birthdate sample queries.

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

([Birthdate],[contactid])

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

([contactid],[Birthdate])

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

([Birthdate])

*/

--Variable declarations

DECLARE @NextDays int,

@CurrentDate datetime,

@NextDate datetime,

@Dy_CurrentDate int,

@Dy_NextDate int,

@CurrentDate_YR int,

@NextDate_YR int,

@StartTime datetime,

@EndTime datetime

--Variable initializations

SELECT @StartTime = GETDATE()

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

--Variables to change, @NextDays and @CurrentDate for testing

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

SELECT @NextDays = 14 -- Change this variable to see Birthdays greater than or equal to current date in days (1-365)

SELECT @CurrentDate = GetDate() --use to test diff values that may be in --DATEPART(dy,GetDate()), i.e. '1984-01-31 23:00:00.000', '2007-12-25 23:00:00.000' , etc.

SELECT @CurrentDate = CAST(FLOOR( CAST( @CurrentDate AS FLOAT ) ) -- Convert to date only..

AS DATETIME

)

SELECT @Dy_CurrentDate = DATEPART(dy,@CurrentDate)

SELECT @NextDate = DATEADD(dy,@NextDays,@CurrentDate)

SELECT @Dy_NextDate = CASE WHEN DATEPART(dy,@NextDate) < @DY_CurrentDate

THEN DATEPART(dy,@NextDate) + 365

ELSE DATEPART(dy,@NextDate)

END

SELECT @CurrentDate_YR = DATEPART(yyyy,@CurrentDate)

SELECT @NextDate_YR = DATEPART(yyyy,@NextDate)

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

--Main SQL Statement for pulling birthday records

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

EXECUTE sp_executesql

N'SELECT ContactId,FirstName, LastName, FullName,emailaddress1,address1_Line1,address1_city,address1_StateorProvince

,address1_PostalCode,birthdate

INTO ##Contact

FROM FilteredContact (NOLOCK)

WHERE isDate(BirthDate) = @isBirthdate

',N'@isBirthdate tinyint',1

-- Add indexes for #Contact for better perf if needed

-- This did not show significant performance enhancement with my testing.

/*

ALTER TABLE ##Contact WITH NOCHECK

ADD CONSTRAINT PK_#Contact PRIMARY KEY CLUSTERED (ContactId)

WITH (FILLFACTOR = 100,PAD_INDEX = ON)

CREATE NONCLUSTERED INDEX CRM_#Contact_LastName on [dbo].[##Contact]

([lastname])

CREATE NONCLUSTERED INDEX CRM_#Contact_Covering on [dbo].[##Contact]

( [FirstName],[LastName],[FullName],[Birthdate])

CREATE NONCLUSTERED INDEX CRM_#Contact_Birthdate_dy on [dbo].[##Contact]

([Birthdate_dy])

CREATE NONCLUSTERED INDEX CRM_#Contact_LastName on [dbo].[#Contact]

([lastname])

*/

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

--Calculations to select Birthdates in next X days if they all fall in the same year

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

IF (@CurrentDate_YR = @NextDate_YR)

BEGIN

SELECT ContactId,FirstName,LastName,FullName,emailaddress1,address1_Line1,address1_city,address1_StateorProvince

,address1_PostalCode, LEFT(DATEADD(dy,0,birthdate),11) AS Birthday

FROM ##Contact

WHERE DATEPART(dy,birthdate) > @DY_CurrentDate and DATEPART(dy,birthdate) < @DY_NextDate -- only works for dates in same yr

ORDER BY DATEPART(dy,birthdate),LastName

DROP Table ##Contact

SELECT @EndTime = Getdate()

END

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

--Calculations to select Birthdates in next X days if they do not fall in the same year

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

ELSE

BEGIN

SELECT ContactId,FirstName,LastName,FullName,emailaddress1,address1_Line1,address1_city,address1_StateorProvince

,address1_PostalCode, LEFT(DATEADD(dy,0,birthdate),11) AS Birthday

FROM ##Contact

WHERE DATEPART(dy,birthdate) > @DY_CurrentDate OR DATEPART(dy,birthdate) < @DY_NextDate-365 --only works for dates spanning yrs

ORDER BY DATEPART(dy,birthdate),LastName

DROP Table ##Contact

SELECT @EndTime = Getdate()

END

/*End of script*/

Sample Output

Note that Fullname, and Address1_Line1, and address1_City, etc. have been omitted from the sample results for purposes of formatting and readability.

 

ContactID FirstName LastName emailaddress1 Birthday
F2C9F901-6542-D911-88AA-0003FFAAEC61 Everrett Coleman EVcole@ex.com Sep 18 1978
9329880F-734C-D911-B5FE-0003FFAAEC61 Linda Ventura Snoopy@exam.com Sep 19 1973
431CFEFF-BB52-D911-B5FD-0003FFAAEC61 Jennifer Craver Jenn@mp.com Sep 21 1972
4082C6CD-057B-D811-BD1B-0003FFAAEC61 Baccus Daniel WineLvr@example.com Sep 22 1970
C3805AED-6936-D911-ADFB-0003FFAAEC61 Tracey Tadeo Mekmo@ex.com Sep 22 1971

Chad Rexin

  • Thanks Chad, that seems to work for me, however in the second case where the birthdays span to years the order of the birthdays are not what I would like. Birthdays in January are displayed before Birthdays in December when querying in December. Surely you want next birthdays first.

    Here's an order clause that works for me:

    ORDER BY

    CASE WHEN DATEPART(dy,birthday) < datepart(dy,GETDATE()) THEN 366+DATEPART(dy,birthday)

    ELSE DATEPART(dy,birthday)

    it adds 366 to all the birthday in the next year and therefor pushes them passed the ones in this year.

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