troubleshooting Question

months person is active

Avatar of sam2929
sam2929 asked on
Oracle Database
11 Comments1 Solution99 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros