Solved

going back 3 years based upon current year

Posted on 2013-10-22
23
308 Views
Last Modified: 2013-10-31
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
Comment
Question by:sam2929
  • 8
  • 4
  • 4
  • +3
23 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:sam2929
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

by:sam2929
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:sam2929
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:sam2929
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:sam2929
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

by:sam2929
Comment Utility
thats right my bad on table naming
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

by:sam2929
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 500 total points
Comment Utility
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

by:sam2929
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now