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.