months person is active

Employee 101 worked two jobs RCD (0,6) shows he worked two jobs

First job RCD =0 and EMPL_STATUS=T he heft 15-08-10

Second Job RCD=6 and EMPL_STATUS=T he left 15-12-12



Records look like this:

EFFDT           EMPLID   RCD   ACTION   START_DT      END_DT     EMPL_STATUS

2007-12-12        101     0     ADL      2007-12-12      null                     A
2014-12-12        101     6     ADL      2014-12-12      null                     A

2015-07-24        101     6     REO      2014-12-12      null                     A

2015-08-12        101     0     ADL      2007-12-12      15-08-10             T



2015-12-24        101     6     TER      2014-12-12      15-12-12     T


Result in two steps

1) Update Active null end_dt termination date


Records look like this:

EFFDT           EMPLID   RCD   ACTION   START_DT      END_DT     EMPL_STATUS

2007-12-12        101     0     ADL      2007-12-12     15-08-10        A--update end_date
2014-12-12        101     6     ADL      2014-12-12     15-12-12        A  --update end_date

2015-07-24        101     6     REO      2014-12-12      null                A

2015-08-12        101     0     ADL      2007-12-12      15-08-10       T



2015-12-24        101     6     TER      2014-12-12      15-12-12     T



2) Want to see how many nonths employee are active for Q3(Starts from October till December)


In this case employee with RCD 6 is active for Q3 for 12 months.


EFFDT           EMPLID   RCD   ACTION   START_DT      END_DT     EMPL_STATUS months_active



2014-12-12        101     6     ADL      2014-12-12     15-12-12      A       12