Monday, March 19, 2012

Happy Birthday, dear Customer!

I hope the title is misleading enough to hide the fact that I am a bit stuck with a requirement.

This actually came in as a comment to an older post on how to use Siebel Query Language and the built in Julian date functions to retrieve a list of all contacts who have their birthday in the current month.

Here is the comment:

"I am looking for a way to search for a specific month/day combination.  Our users want to look for all of our customers with a specific birth date (e.g. March 2nd) and not just all birthdays in the current month.  Is there any way to do this?  Thanks!"


Ok, that should be easy. So I thought...

I tried with julian functions but soon found out that it is quite challenging to find out how many leap years have occurred between the person's birth date and today. Among other studies, I spent a considerable amount of time trying to wrap my brain around this masterpiece of calculations with julian dates.

Simple math to calculate the gregorian date from a julian day number.
Source: Bill Jefferys, University of Texas.
But alas, the Siebel Query Language has no floor() function which would be required to apply the algorithm described in the page mentioned above. So a calculated field or predefined query was out of the question.

So I decided to resort to the last resort: Scripting (eScript has a proper Date() object, hasn't it).

I wrote a nice little business service method (did I mention that I like "nice little" business service methods?) which goes like this (omitting try, catch, etc... for readability):

function IsBirthday (Inputs, Outputs)
{
//instantiate vars for today and birthday
var dTD : Date = new Date();
var dBD : Date = new Date();

//check for empty birth date
if (Inputs.GetProperty("BirthDate") == "")
{
Outputs.SetProperty("isBirthday","N");
}

else
{
//get birth date from Input PS
dBD = new Date(Inputs.GetProperty("BirthDate"));

//if month == month and day == day, we have a jubilee
if (dTD.getMonth() == dBD.getMonth() && dTD.getDate() == dBD.getDate())
{
Outputs.SetProperty("isBirthday","Y");
}
else //maybe another day...
{
Outputs.SetProperty("isBirthday","N");
}
}
}

The method basically takes any date's month and day and compares it with the current day and month. If there is a match, it sets the isBirthday output argument to "Y", otherwise "N".

Then I added a new calculated field to the Contact business component, with the following expression:

IIf([Birth Date] IS NULL,"N",InvokeServiceMethod("AHA Date Service","IsBirthDay","BirthDate='" + [Birth Date] + "'" ,"isBirthday"))

The new calculated field will have a value of "N" if the Birth Date field is empty, otherwise it invokes the business service method I wrote.

The InvokeServiceMethod() function of Siebel Query Language is a nice feature and has had some exposure in various blogs and the documentation. It has four arguments which must be passed as a string (i.e. enclosed in double quotes (")).
  1. The name of the business service
  2. The name of the method
  3. A comma separated list name=value pairs for the input arguments to the method
  4. The name of one output argument, the value of which will be returned by the function
Next, I exposed the new field in the Contact List Applet to see if it works:

Screenshot taken on March 9th (honestly ;-)
So the answer is: Yes, you can calculate if a person has birthday today.

But (and here comes a BIG "but"): When you want to query for all people having birthday today (using the calculated field), then the business service will be invoked once for each record which results in very poor performance.

So this is where I am stuck and ask the Siebel community for help. If you have any idea how to solve the above requirement including optimal performance, please let us know by sharing your knowledge in the comments.

have a nice (birth)day (maybe)

@lex

6 comments:

Andrey Sherman said...

I use two calculated Fields in Contact BC:
1. Birthday: Left([Birth Date],6)
2. Next Birthday: IIF([Birthday] IS NULL,"",IIF([Birthday]>=Left(Today(),6),[Birthday]+Right(Today(),4),[Birthday]+ToChar((1*Right(Today(),4)+1),"####")))

And PDQ "This week birthdays" with search spec "'Contact'.Search = "[Birth Date] IS NOT NULL AND [Next Birthday] <= Today()+7"

And have no performance issues.

Alexander Hansal said...

Hi Andrey,

thanks for sharing this.

I'll give it a try with German locale settings ;-)

have a nice day

@lex

Alexander Hansal said...

Hi again,

with Andrey's tip (I honestly didn't have the balls to use text functions like Left() on a date field ;-), I was able to create a calculated field which computes the "Birthday flag".

This is the formula:

IIf([Birth Date] IS NULL,"N",IIf(Left([Birth Date],6)=Left(Today(),6),"Y","N"))

However, first tests (against the sample db) do not yield satisfactory performance.

have a nice day

@lex

Sebastian. said...

Hi Alex,

we had a similar requirement a few years ago. The client wanted to see all birthday of the next 10 days of his contact.

We started the same way you did with a calc. field but couldnt get a proper performance. Solution to us was a new column and a BS that runs every morning to update the
'birthday flag field'.

Sebastian.

Alexander Hansal said...

Hi Sebastian,

thanks for sharing your insight. Creating a regular batch workflow (or business service) which sets a flag in the physical database seems to be the only solution with optimum performance.

have a nice day

@lex

mroshaw said...

It's funny that you published this, @lex - we've just found out that the vanilla 'Age' field on the Contact BC doesn't work properly! It doesn't take into consideration leap years and just does a straight / 365!

We're having to dig into Julian functions to get this working properly too.

Oli