going back 3 years based upon current year

Take a look at the ADD_MONTHS function.

using a where clause something along the lines of this should limit your data to only those rows in the last 3 years.

where st_dt >= add_months(sysdate, -36)

I agree that add_months will get you 3 years from today.

I'm not understanding the results you expect.

Where does s_position come into the picture?

The results look like they can be derived from s_emp only.

Take the 3 years from today and st_dt-1 then st_dt to end_dt.

Please clarify the logic.

No we can't do it reason in some cases S_POSITION and S_EMP date are in sync in that case
not change needed but in some cases we have scenarios where s_position date start before
s_emp in that cases we need to get only 3 year back date from s_position

I do not understand what you just said.

What is considered 'in sync' and not 'in sync'?

I still don't see a reason to join against S_POSITION.

Can you post more sample data with expected results that show the need to access S_POSITION?

case 2: No action required in below case

S_POSITION:

pos_tcd           st_dt                   end_dt

101      06-FEB-2010      31-DEC-9999



S_EMP  as below:

pos_tcd             st_dt                 end_dt

101      06-FEB-2010      31-DEC-9999

S_POS_EMP:

pos_tcd             st_dt                 end_dt
101            06-FEB-2010      31-DEC-9999

So, you only need the second row in the result set it there is at least one row in s_position that is less than st_dt in s_emp and then only for the last 3 years?

What if the min st_dt in s_position is less than st_dt in s_emp but not greater than 3 years?

What is the result of:

S_POSITION:

pos_tcd           st_dt                   end_dt

10063320      06-DEC-2011      31-DEC-2011
10063320      01-JAN-2012      31-DEC-9999


S_EMP:

pos_tcd             st_dt                 end_dt

10063320      06-FEB-2012      31-DEC-9999

Select allOpen in new window

Result should be :

S_POS_EMP:

pos_tcd             st_dt                 end_dt

10063320      06-DEC-2011      05-FEB-2012

10063320      06-FEB-2012      31-DEC-9999

Combining the question data and case 2, the following result:
& when run: sysdate - 36 = 23 Oct 2010

|  POS_TCD |                           ST_DT |                          END_DT |
|----------|---------------------------------|---------------------------------|
|      101 |  October, 23 2010 00:00:00+0000 | December, 31 9999 00:00:00+0000 |
| 10063320 |  October, 23 2010 00:00:00+0000 | February, 05 2012 00:00:00+0000 |
| 10063320 | February, 06 2012 00:00:00+0000 | December, 31 9999 00:00:00+0000 |

Select allOpen in new window

Was produced by the following query:

SELECT
        pos_tcd
      , st_dt
      , CASE WHEN lead(st_dt) over (partition BY pos_tcd ORDER BY st_dt, end_dt) - 1 < end_dt
             THEN lead(st_dt) over (partition BY pos_tcd ORDER BY st_dt, end_dt) - 1
             ELSE end_dt
        END
        AS end_dt
FROM (
      SELECT
              pos_tcd
            , greatest(st_dt,add_months(trunc(sysdate),-36)) st_dt
            , end_dt
      FROM s_position
      WHERE end_dt > add_months(trunc(sysdate),-36)

      UNION

      SELECT
              pos_tcd
            , greatest(st_dt,add_months(trunc(sysdate),-36)) st_dt
            , end_dt
      FROM s_emp
      WHERE end_dt > add_months(trunc(sysdate),-36)
      )
;    	

-- and data used was:



    CREATE TABLE S_POSITION
    	(POS_TCD int, ST_DT timestamp, END_DT timestamp)
    ;
    
    INSERT ALL 
    	INTO S_POSITION (POS_TCD, ST_DT, END_DT)
    		 VALUES (10063320, '01-Jan-2006 12:00:00 AM', '09-Aug-2006 12:00:00 AM') 
    	INTO S_POSITION (POS_TCD, ST_DT, END_DT)
    		 VALUES (10063320, '10-Aug-2006 12:00:00 AM', '05-Dec-2009 12:00:00 AM')
    	INTO S_POSITION (POS_TCD, ST_DT, END_DT)
    		 VALUES (10063320, '06-Dec-2009 12:00:00 AM', '31-Dec-2009 12:00:00 AM')
    	INTO S_POSITION (POS_TCD, ST_DT, END_DT)
    		 VALUES (10063320, '01-Jan-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
    	INTO S_POSITION (POS_TCD, ST_DT, END_DT)
    		 VALUES (101, '06-Feb-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
    SELECT * FROM dual
    ;
    
    CREATE TABLE S_EMP
    	(POS_TCD int, ST_DT timestamp, END_DT timestamp)
    ;
    
    INSERT ALL 
    	INTO S_EMP (POS_TCD, ST_DT, END_DT)
    		 VALUES (10063320, '06-Feb-2012 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
    	INTO S_EMP (POS_TCD, ST_DT, END_DT)
    		 VALUES (101, '06-Feb-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
    SELECT * FROM dual
    ;

: http://sqlfiddle.com/#!4/e8f08/1 

Select allOpen in new window

Case 1 and 2 are good but we also discuss case 3 and that id failing now.

S_POSITION:

pos_tcd           st_dt                   end_dt

10063321      06-DEC-2011      31-DEC-2011
10063321      01-JAN-2012      31-DEC-9999


S_EMP:

pos_tcd             st_dt                 end_dt

10063321      06-FEB-2012      31-DEC-9999



S_POS_EMP:

pos_tcd             st_dt                 end_dt

10063321      06-DEC-2011      05-FEB-2012

10063321      06-FEB-2012      31-DEC-9999


Insert st mt is below

CREATE TABLE S_POSITION
      (POS_TCD int, ST_DT timestamp, END_DT timestamp)
;

INSERT ALL
      INTO S_POSITION (POS_TCD, ST_DT, END_DT)
             VALUES (10063320, '01-Jan-2006 12:00:00 AM', '09-Aug-2006 12:00:00 AM')
      INTO S_POSITION (POS_TCD, ST_DT, END_DT)
             VALUES (10063320, '10-Aug-2006 12:00:00 AM', '05-Dec-2009 12:00:00 AM')
      INTO S_POSITION (POS_TCD, ST_DT, END_DT)
             VALUES (10063320, '06-Dec-2009 12:00:00 AM', '31-Dec-2009 12:00:00 AM')
      INTO S_POSITION (POS_TCD, ST_DT, END_DT)
             VALUES (10063320, '01-Jan-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
      INTO S_POSITION (POS_TCD, ST_DT, END_DT)
             VALUES (101, '06-Feb-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
                INTO S_POSITION (POS_TCD, ST_DT, END_DT)
                 VALUES (10063321, '01-Jan-2011 12:00:00 AM', '09-Aug-2006 12:00:00 AM')
          INTO S_POSITION (POS_TCD, ST_DT, END_DT)
                 VALUES (10063321, '10-Aug-2006 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
SELECT * FROM dual
;


CREATE TABLE S_EMP
      (POS_TCD int, ST_DT timestamp, END_DT timestamp)
;

INSERT ALL
      INTO S_EMP (POS_TCD, ST_DT, END_DT)
             VALUES (10063320, '06-Feb-2012 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
      INTO S_EMP (POS_TCD, ST_DT, END_DT)
             VALUES (101, '06-Feb-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
INTO S_EMP (POS_TCD, ST_DT, END_DT)
             VALUES (10063321, '06-Feb-2012 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
SELECT * FROM dual
;

Expected results given:
case 1
pos_tcd             st_dt                 end_dt
10063320      22-OCT-2010*      05-FEB-2012
10063320      06-FEB-2012      31-DEC-9999

case 2
pos_tcd             st_dt                 end_dt
101            06-FEB-2010*      31-DEC-9999

case 3
pos_tcd             st_dt                 end_dt
10063321      06-DEC-2011*     05-FEB-2012
10063321      06-FEB-2012      31-DEC-9999

* why is the case 1 st_dt at 22-OCT-2010 but not case 2 & 3?
please explain in words


This result:

|  POS_TCD |              ST_DT |             END_DT |
|----------|--------------------|--------------------|
|      101 |  October, 23 2010  | December, 31 9999  |
| 10063320 |  October, 23 2010  | February, 05 2012  |
| 10063320 | February, 06 2012  | December, 31 9999  |
| 10063321 |  October, 23 2010  | February, 05 2012  |
| 10063321 | February, 06 2012  | December, 31 9999  |

Select allOpen in new window

from the previous query, but updated data
http://sqlfiddle.com/#!4/0c610/1

case 1: If s_pos and s_emp_assign st_date are not same and s_pos start date is greater then 3 year then we want s_pos date to be restricted to three year only. Example 10063320

case 2: If s_pos and s_emp_assign st_date are not same and s_pos start date is less then 3 year then no action needed as s_pos is already less then three year. Example 10063321

case3 :if s_pos and s_emp_assign start dates are same then no action is needed that is 101

do you mean s_position and s_emp? (s_emp_assign is the result isn't it?)
& so these align with the sequence of the question:

case 1: If s_position and s_emp st_date are not same and s_pos start date is greater then 3 year then we want s_pos date to be restricted to three year only. Example 10063320

case 2 :if s_position and s_emp  start dates are same then no action is needed that is 101


case 3: If s_position and s_emp  st_date are not same and s_pos start date is less then 3 year then no action needed as s_pos is already less then three year. Example 10063321

thats right my bad on table naming

sorry I'm going to have to leave this for now  
- but it looks like the approach I was following isn't going to work.

is it possible for s_emp to have more than one row for a given pos_tcd or is that the unique key?

is it possible for s_position to have more than row for a given (pos_tcd, st_dt) pair or is that the unique key?

this data doesn't look correct


 VALUES (10063321, '01-Jan-2011 12:00:00 AM', '09-Aug-2006 12:00:00 AM')

it starts in 2011 but ends in 2006?

That's a pretty aggressive schedule to finish 5 years before you start.

Yes S_EMP can have multiple rows but we need to match to min start date from S_EMP with S_POSITION

we can have rows like below:

S_EMP:

pos_tcd             st_dt                 end_dt

10063321      06-FEB-2012     07-JULY-2013
10063321       08-JULY-2013   31-DEC-9999

S_POSITION:

pos_tcd           st_dt                   end_dt

10063321      06-DEC-2011      31-DEC-2011
10063321      01-JAN-2012      31-DEC-9999

In this case we get two rows from S_EMP and S_POSITION will not change:

S_POS_EMP:

pos_tcd             st_dt                 end_dt

10063321      06-DEC-2011      05-FEB-2012

10063321      06-FEB-2012     07-JULY-2013
10063321       08-JULY-2013   31-DEC-9999

what about my other 2 questions?

and for s_emp,  is (pos_tcd, st_dt)  unique?

also, all of the times are in days truncated to midnight.  Will that always be the case?

and,  if I had a range of Nov 1-Nov10 followed by Nov 11-Nov20,  would those be considered as a single range Nov 1- Nov 20?  This relates to previous question.

If you might have time values, then extend the example to lowest time resolution you use.

If hourly, are sequential hours considered a single range?
If minutely, are sequential minutes considered a single range?

with cte as
(select p.pos_tcd, min(p.st_dt) pst, min(p.end_dt) pend, min(e.st_dt) est, min(e.end_dt) eend
 from s_position p, s_emp e
 where p.pos_tcd = e.pos_tcd
 group by p.pos_tcd)
select pos_tcd, est st_dt, eend end_dt from cte
union
select pos_tcd, greatest(pst,add_months(trunc(sysdate),-36)), est - 1 from cte where pst < est;

See attached file showing results
results.txt

for s_emp,  is (pos_tcd, st_dt)  unique?  yes

also, all of the times are in days truncated to midnight.  Will that always be the case?

start date and end date columns are date and not timestamps so - 36 days should be ok.

and,  if I had a range of Nov 1-Nov10 followed by Nov 11-Nov20,  would those be considered as a single range Nov 1- Nov 20?  This relates to previous question.

I would prefer two ranges for S_EMP .

Having trouble posting. Change the first query in the union statement to "select pos_tcd, st_dt, end_dt from s_emp" and it should work.

sam2929 your reply to one of my questions didn't really anser it


>>> also, all of the times are in days truncated to midnight.  Will that always be the case?

  DATE vs TIMESTAMP data types aren't what I was asking.  Do your values have time values other than 00:00:00  (remember DATE types have time too)