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. |
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 (")).
- The name of the business service
- The name of the method
- A comma separated list name=value pairs for the input arguments to the method
- The name of one output argument, the value of which will be returned by the function
 |
| Screenshot taken on March 9th (honestly ;-) |
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
