Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

going back 3 years based upon current year

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
sam2929
Asked:
sam2929
  • 8
  • 4
  • 4
  • +3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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)
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
sam2929Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
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
 
sam2929Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

Open in new window

0
 
sam2929Author Commented:
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
 
PortletPaulCommented:
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 |

Open 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 

Open in new window

0
 
sam2929Author Commented:
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
 
PortletPaulCommented:
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  |

Open in new window

from the previous query, but updated data
http://sqlfiddle.com/#!4/0c610/1
0
 
sam2929Author Commented:
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
 
PortletPaulCommented:
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
 
sam2929Author Commented:
thats right my bad on table naming
0
 
PortletPaulCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sam2929Author Commented:
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
 
sdstuberCommented:
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
 
awking00Commented:
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
 
awking00Commented:
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
 
sam2929Author Commented:
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
 
awking00Commented:
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
 
sdstuberCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now