Solved

# going back 3 years based upon current year

Posted on 2013-10-22
Medium Priority
366 Views
I have Position table as below:

S_POSITION:

pos_tcd           st_dt                   end_dt

10063320      01-JAN-2006      09-AUG-2006
10063320      10-AUG-2006      05-DEC-2009
10063320      06-DEC-2009      31-DEC-2009
10063320      01-JAN-2010      31-DEC-9999

S_EMP  as below:

pos_tcd             st_dt                 end_dt

10063320      06-FEB-2012      31-DEC-9999

In S_POSITION the earliest start date is 01-JAN-2006 and the latest end date is 31-DEC-9999.

In S_EMP, the earliest start date is 06-FEB-2012 and the latest end date is 31-DEC-9999.

Starting at 01-JAN-2006 until 05-FEB-2012 since there would be no employee in S_EMP
so joining two tables i want result like below but we only want to get position back three year only from S_POS so end result i want is

S_POS_EMP:

pos_tcd             st_dt                 end_dt

10063320      22-OCT-2010      05-FEB-2012
10063320      06-FEB-2012      31-DEC-9999

will change to  22-OCT-2010  as we just want current year - 3 years
0
Question by:sam2929
• 8
• 4
• 4
• +3

LVL 23

Expert Comment

ID: 39592741
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.

0

LVL 78

Expert Comment

ID: 39592753
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.

0

Author Comment

ID: 39592797
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
0

LVL 78

Expert Comment

ID: 39592808
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?
0

Author Comment

ID: 39592864
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
0

LVL 78

Expert Comment

ID: 39592871
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
``````
0

Author Comment

ID: 39592894
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
0

LVL 49

Expert Comment

ID: 39593033
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 |
``````
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
, end_dt
FROM s_position

UNION

SELECT
pos_tcd
, end_dt
FROM s_emp
)
;

-- 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
``````
0

Author Comment

ID: 39593628
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
;
0

LVL 49

Expert Comment

ID: 39593651
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?

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  |
``````
from the previous query, but updated data
http://sqlfiddle.com/#!4/0c610/1
0

Author Comment

ID: 39593812
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
0

LVL 49

Expert Comment

ID: 39593895
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
0

Author Comment

ID: 39593931
thats right my bad on table naming
0

LVL 49

Expert Comment

ID: 39593935
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.
0

LVL 74

Expert Comment

ID: 39594184
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?
0

LVL 74

Expert Comment

ID: 39594246
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.
0

Author Comment

ID: 39594276
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
0

LVL 74

Expert Comment

ID: 39594427
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?
0

LVL 32

Expert Comment

ID: 39594479
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
0

LVL 32

Accepted Solution

awking00 earned 2000 total points
ID: 39594480
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
0

Author Comment

ID: 39594491
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 .
0

LVL 32

Expert Comment

ID: 39594530
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.
0

LVL 74

Expert Comment

ID: 39608754
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)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
###### Suggested Courses
Course of the Month3 days, 9 hours left to enroll