complex aggregation

[#1]

hi

i have a table with all data for an employee like his hiring, redesignation , leaving with two dates maintained for each record

Employee	Start Date	EndDate	Status	Action
A	26-08-1990	10-04-2011	Active	Hiring
A	11-04-2011	29-10-2011	Active	Redesignation
A	30-10-2011	31-12-9999	Withdrawn	Leaving
B	01-01-2011	31-12-9999	Active	Hiring
C	30-10-2002	31-12-2003	Active	Hiring
C	01-01-2004	31-12-9999	Active	Redesignation

i want to calculate duration of service for each employees.

How to achieve this ?

[tresesco]

Can the logic be like - (Max of EndDate - Min of StartDate) after grouping Dates on Employee?

Is this what you want?

Regards, tresesco

[jagan]

Hi,

Use the script below

Arrive a new table for calculating days serviced.

LOAD

     Employee,

     Max(Num(EndDate)) - Min(Num(StartDate)) AS DaysServiced

FROM DataSource

GROUP BY Employee;

Hope this helps you

Regards,

Jagan.

[jagan]

Hi,

Use the script below

LOAD

     Employee,

     EndDate,

     StartDate,

     Status,

     Action,

    Num(EndDate) - Num(StartDate) AS DaysServiced

FROM DataSource

GROUP BY Employee;

Hope this helps you

Regards,

Jagan.

[vijay_iitkgp]

Can you please let us know what should be duration for Employee A.

[#6]

the duration of employee A  should be (start date when action is leaving - start date when action is hiring) i.e 30-10-2011 - 26-08-

and if the employee is still active till the last record his duration sho1990uld be calculated from today - start date of hiring

[#7]

thanks jagan
but do u think that there should be some check on status so that the right value is calculated because the end date is not showing the correct picture , employee hiredate and enddate is maintained in startdate attribute

[vijay_iitkgp]

Hi ,

You can use this Code:

Table1:

Load

Employee,

StartDate as HiringDate

From Table Where Action=Hiring;

Left Join

Load

Employee,

Date(Max(StartDate),'DD-MM-YYYY') as LatestDate

From Table Group By Employee;

Left Join

Load

Employee,

Date(StartDate,'DD-MM-YYYY') as LatestDate,

Status,

Action

From Table Group;

Table2:

Noconcatenate

Load

*,

If(Status=Active,Today-HiringDate,Action='Leaving',LatestDate-HiringDate) as Age

Resident Table1;

Drop Table1;

Hope this will help

[CELAMBARASAN]

Hi,

     Try with this

Temp:
Load * Inline
[
Employee,StartDate,EndDate,Status,Action
A,26/08/1990,10/04/2011,Active,Hiring
A,11/04/2011,29/10/2011,Active,Redesignation
A,30/10/2011,31/12/9999,Withdrawn,Leaving
B,01/01/2011,31/12/9999,Active,Hiring
C,30/10/2002,31/12/2003,Active,Hiring
C,01/01/2004,31/12/9999,Active,Redesignation];
serviceDateTable:
Load
          Employee,
          Min(StartDate) as ServiceStart,
          Max(StartDate) as ServiceEnd
Resident
          Temp
Group by Employee;
inner Join(serviceDateTable)
Load
          Employee,
          StartDate as ServiceEnd,
          Status as CurrentStatus
Resident
          Temp;
Left Join(serviceDateTable)
Load
          Employee,
          if(CurrentStatus='Active',interval(Today() - ServiceStart,'D'),interval(ServiceEnd-ServiceStart,'D')) as ServiceDays
Resident
          serviceDateTable;

Hope it helps

Celambarasan

[jagan · Accepted solution]

Hi,

Try below script,

TempTable:

Load

Employee,

StartDate as HiringDate

From Table Where Action='Hiring';

Left Join

Load

Employee,

StartDate as LeavingDate

From Table Where Action='Leaving';

Data:

Load

Employee,

If(IsNull(LeavingDate), Num(Today()) - Num(HiringDate), Num(LeavingDate) - Num(HiringDate) AS      ServiceDays

RESIDENT TempTable;

DROP TABLE TempTable;

Hope this helps you.

Regards,

Jagan.