Contractual Date Spans for an Employee

[samit_9999]

Hi,

I have employees who have contracts with Mobile Cariers
Contracts are of 2 type
1) Continuous Span- where the employee is having contracts without a break
Employee Eff Date End Date
10 01/01/2004 12/31/2004
10 01/01/2005 12/31/2005
10 01/01/2006 06/30/2006

2) Non Continuous Span- where the employee has break periods in his contracts
Employee Eff Date End Date
10 01/01/2004 12/31/2004
10 02/01/2006 06/30/2006
In the above case the employee had no contracts between 01/01/2005 and 01/31/2006

If there is a continuous contract, here are the results expected
Employee Eff Date End Date
10 01/01/2004 06/30/2006
i.e min of Eff Date and max of End Date

If however there is a Non-Continuou Contract , here are results expected
Employee Eff Date End Date
10 02/01/2006 06/30/2006
i.e max of Eff Date and max of End Date

The only way to know if the employee has Continuous or Non-Continuous Contracts is by looking at the dates . There is no indicator for the same.

Can this be acheived in SQL.If not what would be the best approach in DataStage to achieve the above results.

Thanks much in advance.

Sam

[ray.wurlod]

Whatever you do is going to require two passes through the data (a nested query in SQL), the first to determine whether continuous or not and the second to apply the appropriate calculation (MIN or MAX on EffDate) probably using a CASE construct in SQL.

In DataStage you'd total the contract days for each employee and find min EffDate and max EffDate in an Aggregator stage, then compare the total contract days with the difference of dates to determine whether continuous or not. Output the total contract days for continuous contract, lookup on EmpNo and maxEffDate for non-continuous.

[DSguru2B]

What database are you using?
If its in oracle then you can use the Lag() and Lead() functions to get the previous and next values respectively. Look at the following website on more details on these functions
http://www.adp-gmbh.ch/ora/sql/analytical/lag.html