Calculate age data coming from AS/400

[monsky]

Hi guys,

We're trying to figure out if it's possible to calculate the age of a policy by getting the difference between the current date and date issued. We want the difference to be in terms of months.

Currently we're doing this calculation via stored procedure in Sybase after loading. We want to do away with this stored procedure and calculate the polcy age on the fly.

I'd appreciate if you guys have suggestions on this.

 

[JGirl]

Hi,

Sorry if this isnt much of a reply - My stored procedure knowledge is mininal....

Coould you just create a custom function (or perhaps the correct terminology is 'routine') in DataStage that you pass the policy date to and it calculates the months between the system date (Date()) and the policy date. The logic that you use in the function would be same logic that your stored procedure uses (but obviously you would need to change the syntax for datastage)?

Then when you pass the data through your transformer stage, you could add a new column to the output data, and the derivation of the column would the the value that is generated by using the function you created on the policy date.

J

 

[JGirl]

OK....I've had a chance to think about this a bit more...

The following will calculate the number of complete months between the current date and the policy start date.

I haven't used datastage for a while, so i might be a bit rusty, but try this (and i'll assume that your dates are stored in the datastage internal format):

1. Create a new function called PolicyAgeInMonths. This function will take in one argument, PolicyStartDate (a Datastage internal date).

2. The code for the function will be something like the following:

** Reset MonthsBetween variable
MonthsBetween = 0

** Convert today's and policy date into external yyyy-mm-dd format
TodaysDate 	= Oconv(Date(), D-YMD[4,2,2])
PolicyDate 	= Oconv(PolicyStartDate,D-YMD[4,2,2])

** Extract each portion of the dates into separate variables
TodaysDay 	= TodaysDate[9,2]
TodaysMonth 	= TodaysDate[6,2]
TodaysYear	= TodaysDate[1,4]

PolicyDay 	= PolicyDate[9,2]
PolicyMonth 	= PolicyDate[6,2]
PolicyYear	= PolicyDate[1,4]

** Calculate the months between the 2 dates
YearDiff = TodaysYear - PolicyYear
MonthDiff = TodaysMonth - PolicyMonth

If (TodaysDay >= PolicyDay) Then
	MonthsBetween = YearDiff * 12 + MonthDiff	
Else
	MonthsBetween = YearDiff * 12 + MonthDiff - 1

** Return the months between (integer)
Return Months Between

3. In your job's transformer stage, create a new output column called PolicyAge (or whatever you want), and set the derivation of the column to PolicyAgeInMonths(xx) where xx is the name of the input column containing the policy start date.

Hope this helps.
J

 

[BocaBurger]

If you have DS 7.5.z, you can get MapStage where calculations like this are easy using the internal date & time functions.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!