Find true field start/end date from date tracked tables like per_people_f, per_assignments_f

There's time you want to know what are the start date/end date an employee stay within a job, sometimes it is not an easy task because an employee may have multiple assignments records because of org change, supervisor change or so, even though all assignments records have same job. Here is a way to find out what is the true job (or other fields) start/end date which is using analytic function. 

Here is the example of data in Assginments table with a group id. The group id is a date, it is calculated using This analytic function: 

--+++++++++++++++++++++++++++++++++++++++++++++
effective_end_date-sum(effective_end_date-effective_start_date+1) over(partition by person_id, job_id order by effective_start_date) +1 group_id

--+++++++++++++++++++++++++++++++++++++++++++++


id        job_id    date_start    date_end      group_id

814    12219    8/22/2005    3/31/2006    8/22/2005

814    12579    7/1/2007    9/30/2007    7/1/2007

814    12579    10/1/2007    10/21/2007    7/1/2007

814    12579    10/22/2007    3/31/2008    7/1/2007

814    12579    4/1/2008    1/7/2010    7/1/2007

814    12579    1/8/2010    2/21/2010    7/1/2007

814    12579    2/22/2010    5/31/2010    7/1/2007

814    12579    6/1/2010    6/23/2010    7/1/2007

814    12579    6/24/2010    3/31/2012    7/1/2007

814    12579    4/1/2012    5/6/2012    7/1/2007

814    12579    5/7/2012    10/1/2013    7/1/2007

814    12579    5/5/2014    12/31/4712    2/1/2008

814    12622    4/1/2006    3/31/2007    4/1/2006

814    12622    4/1/2007    4/5/2007    4/1/2006

814    12623    4/6/2007    6/30/2007    4/6/2007

814    12623    2/18/2014    5/4/2014    11/24/2013


Please pay attention to that the same job ids 12579 is in different groups, one group is 7/1/2007 and another is 2/1/2008. The group id does not mean a real date, it is just the result of a calculation and is used to differenciate job range. 

As soon as you generate the list, you can now using group by to generate the range of each job group: 

814    12219    8/22/2005    3/31/2006
814    12622    4/1/2006    4/5/2007

814    12623    4/6/2007    6/30/2007

814    12579    7/1/2007    10/1/2013

814    12623    2/18/2014    5/4/2014
814    12579    5/5/2014    12/31/4712


Here is the query: 

select person_id,job_id,min(effective_start_date) job_start_date, max(effective_end_date) job_end_date

from    (select     person_id, job_id, effective_start_date, effective_end_date

                       , effective_end_date-sum(effective_end_date-effective_start_date+1) over (partition by person_id, job_id order by effective_start_date) +1 group_id

            from     per_assignments_f

            where   assignment_status_type_id in

                                   --only pick up those assignments active assignments

                                                 (1 --Active Assignment

                                                 ,2 --Active Contingent Assignment

                                                 ,3 --Leave with Pay Assignment

                                                 ,4 --Leave without Pay Assignment

                                                 ,5)

             and person_id=814

             )TMP

group by person_id,job_id,group_id