Solved

exclude data greater then 3 year

Posted on 2013-10-31
26
260 Views
Last Modified: 2013-12-04
Hi,
I have two table  S_EMP_ASSIGN and S_POSITION

S_EMP_ASSIGN:

query:

select POSITION_TCD,POSITION_HOLDER,emp_assign_start_dt,emp_assign_end_dt from S_EMP_ASSIGN
where position_tcd in ('10069587','10070988','10084336','10083103','10080191','10009198')

10009198      108695      04-04-29      09-03-25
10080191      107449      09-09-08      10-06-30
10080191      109571      09-01-05      09-09-07
10083103      133780      10-05-10      99-12-31
10084336      101267      10-03-08      11-03-20

query:

select position_tcd,pos_start_dt,pos_end_date from S_POSITION
where position_tcd in ('10069587','10070988','10084336','10083103','10080191','10009198')

S_POSITION:

10009198      50-01-01      09-03-25
10069587      06-12-05      08-07-21
10070988      07-07-02      08-04-30
10070988      08-05-01      99-12-31
10080191      09-01-05      10-06-30
10083103      09-10-19      99-12-31
10084336      10-02-08      11-03-28

I want end result as below:

Position      Position earliest Start Date      Position latest End Date      Expected START DATE      Expected END DATE      EXIST IN S_EMP_ASSIGN
10069587      Dec 5, 2006      Jul 21, 2008      Excluded from query      Excluded from query      No
10070988      May 1, 2008      Dec 31, 9999      Jan 1, 2010      Dec 31 9999      No
10084336      Feb 8, 2010      Mar 28, 2011      Feb 8, 2010      Mar 28, 2011      Yes
10083103      Oct 19, 2009      Dec 31, 9999      Jan 1, 2010      Dec 31 9999      Yes
10080191      Jan 5, 2009      Jun 30, 2010      Jan 5, 2009      Jun 30, 2010      Yes
10009198      Jan 1, 1950      Mar 25, 2009      Jan 1, 2010      Mar 25, 2009      Yes

We want to exclude anything less then three 3 years:

the definition of 3 years is Jan 1 of the current year minus 3 years. So for Oct 30, 2013, the starting point of the vacancy calendar is Jan 1, 2010.
 
The start date of the vacancy calendar should be the earliest of:
 
a)      Jan 1 of current year, minus 3 years
 
b)      S_POSITION Start date
 
The end date of the vacancy calendar should be the latest of:
 
a)      Current date
 
b)      S_POSITION End date
0
Comment
Question by:sam2929
  • 15
  • 8
26 Comments
 

Author Comment

by:sam2929
ID: 39615590
Not it is not duplicate the definition of  3 year has changed so also there some additional requirement up to you if you want to delete old one
0
 

Author Comment

by:sam2929
ID: 39615681
lets start baby steps :

When i run below query i get below results  :

10070988      No Holder      N      07-07-02      08-04-30
10070988      No Holder      N      08-05-01      99-12-31

all i want is to start date as 10-1-1  so i want results as below:

10070988      No Holder      N      10-01-01      08-04-30
10070988      No Holder      N      08-05-01      99-12-31

sql is below:

SELECT
POSITION_TCD,
'No Holder' POSITION_HOLDER,
--CASE WHEN POS_START_DT < TO_CHAR('20100101,'YYYYMMDD') THEN ADD_MONTHS(CURRENT_DATE,-36) ELSE POS_START_DT END  EMP_ASSIGN_START_DT,
CASE WHEN ADD_MONTHS(TO_CHAR('20100101'),-36) < POS_START_DT THEN 'N' ELSE 'Y' END TEST,
POS_START_DT,
POS_END_DATE AS EMP_ASSIGN_END_DT
FROM  S_POSITION
where position_tcd not in (select distinct POSITION_TCD from S_EMP_ASSIGN)
and ADD_MONTHS(TO_CHAR('20100101'),-36) < POS_START_DT
and POSITION_TCD IN ('10084336','10069587','10070988')
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39615906
@slightwv, the expected result are quite different

@sam2929
I do not believe you have described the require rules correctly.
and: please, please, please, use YYYY-MM-DD (4 digit years)
and suggest you don't jump from one date format to another in your question

Here are your expected results using YYYY-MM-DD and ordered by position number - you may want to verify them
POSITION      POSEARLTST      POSLATEEND      EXPECTST      EXPECTEND      INS_EMP_ASSIGN
10009198      1950-01-01      2009-03-25      2010-01-01    2009-03-25      Yes
10069587      2006-12-05      2008-07-21                                    No 
10070988      2008-05-01      9999-12-31      2010-01-01    9999-12-31      No
10080191      2009-01-05      2010-06-30      2009-01-05    2010-06-30      Yes
10083103      2009-10-19      9999-12-31      2010-01-01    9999-12-31      Yes
10084336      2010-02-08      2011-03-28      2010-02-08    2011-03-28      Yes

Open in new window

So, lets compare these to one of your stated business rules:
The start date of the vacancy calendar should be the earliest of:
a)      Jan 1 of current year, minus 3 years
b)      S_POSITION Start date
but there are many s_postion start dates that are earlier than 2010-01-01 see column EXPECT_ST_LEAST
| POSITION_TCD | POS_EARLY_ST | POS_LATE_END | EXPECT_ST_LEAST | EXPECT_ST_GREATEST |
|--------------|--------------|--------------|-----------------|--------------------|
|     10009198 |   1950-01-01 |   2009-03-25 |      1950-01-01 |         2010-01-01 |
|     10069587 |   2006-12-05 |   2008-07-21 |      2006-12-05 |         2010-01-01 |
|     10070988 |   2008-05-01 |   9999-12-31 |      2008-05-01 |         2010-01-01 |
|     10080191 |   2009-01-05 |   2010-06-30 |      2009-01-05 |         2010-01-01 |
|     10083103 |   2009-10-19 |   9999-12-31 |      2009-10-19 |         2010-01-01 |
|     10084336 |   2010-02-08 |   2011-03-28 |      2010-01-01 |         2010-02-08 |

Open in new window

I believe the business rule is closer to:
The start date of the vacancy calendar should be the latest of:
a)      Jan 1 of current year, minus 3 years
b)      S_POSITION Start date
see: column EXPECT_ST_GREATEST above

Yet your expected results shows 2009-01-05 in this column - why? - this also needs describing in the rule.

Even then there is no explanation of why dates should be absent for 10069587, for example 10009198 also has a position end before sysdate - so why does it get dates displayed but not 10069587?

Please verify that the "expected results" are correct and that your description of "business rules" are accurate and complete.
-------------------------

This is as close as I can currently get to your expected results until business rule anomalies are explained:
| POSITION_TCD | POS_EARLY_ST | POS_LATE_END | EXPECT_ST_GREATEST | EXPECT_END | IN_S_EMD_ASSIGN |
|--------------|--------------|--------------|--------------------|------------|-----------------|
|     10009198 |   1950-01-01 |   2009-03-25 |         2010-01-01 | 2013-11-01 |             Yes |
|     10069587 |   2006-12-05 |   2008-07-21 |         2010-01-01 | 2013-11-01 |              No |
|     10070988 |   2008-05-01 |   9999-12-31 |         2010-01-01 | 9999-12-31 |              No |
|     10080191 |   2009-01-05 |   2010-06-30 |         2010-01-01 | 2013-11-01 |             Yes |
|     10083103 |   2009-10-19 |   9999-12-31 |         2010-01-01 | 9999-12-31 |             Yes |
|     10084336 |   2010-02-08 |   2011-03-28 |         2010-02-08 | 2013-11-01 |             Yes |

Open in new window

using the query below (DDL/DML included):
WITH
 less3 AS (
            SELECT
                   to_date(to_char(EXTRACT (YEAR FROM sysdate)-3) || '-01-01','YYYY-MM-DD')
                   AS dt
            FROM dual
          )

SELECT
        p.position_tcd
     ,  to_char(min(p.pos_start_dt),'YYYY-MM-DD') pos_early_st
     ,  to_char(max(p.pos_end_date),'YYYY-MM-DD') pos_late_end
     ,  to_char(greatest(less3.dt,min(p.pos_start_dt)),'YYYY-MM-DD') AS expect_st_greatest
     ,  to_char(greatest(sysdate,max(p.pos_end_date)),'YYYY-MM-DD')  AS expect_end
     , CASE WHEN a.position_tcd IS NULL THEN 'No' ELSE 'Yes' END     AS in_s_emd_assign
FROM (
      SELECT
             s_position.*
            , row_number() over (partition BY position_tcd
                                 ORDER BY pos_start_dt DESC ) AS rn
      FROM s_position
     ) p
LEFT JOIN s_emp_assign a ON p.position_tcd = a.position_tcd
CROSS JOIN less3
WHERE p.rn = 1
GROUP BY
        p.position_tcd
     ,  less3.dt
     , CASE WHEN a.position_tcd IS NULL THEN 'No' ELSE 'Yes' END
ORDER BY
       p.position_tcd
;

CREATE TABLE S_EMP_ASSIGN
	(POSITION_TCD int, POSITION_HOLDER int, EMP_ASSIGN_START_DT timestamp, EMP_ASSIGN_END_DT timestamp)
;

INSERT ALL 
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10009198, 108695, '29-Apr-2004 12:00:00 AM', '25-Mar-2009 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10080191, 107449, '08-Sep-2009 12:00:00 AM', '30-Jun-2010 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10080191, 109571, '05-Jan-2009 12:00:00 AM', '07-Sep-2009 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10083103, 133780, '10-May-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10084336, 101267, '08-Mar-2010 12:00:00 AM', '20-Mar-2011 12:00:00 AM')
SELECT * FROM dual
;

CREATE TABLE S_POSITION
	(POSITION_TCD int, POS_START_DT timestamp, POS_END_DATE timestamp) 
;

INSERT ALL 
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10009198, '01-Jan-1950 12:00:00 AM', '25-Mar-2009 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10069587, '05-Dec-2006 12:00:00 AM', '21-Jul-2008 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10070988, '02-Jul-2007 12:00:00 AM', '30-Apr-2008 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10070988, '01-May-2008 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10080191, '05-Jan-2009 12:00:00 AM', '30-Jun-2010 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10083103, '19-Oct-2009 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10084336, '08-Feb-2010 12:00:00 AM', '28-Mar-2011 12:00:00 AM')
SELECT * FROM dual
;

http://sqlfiddle.com/#!4/878e7/1

Open in new window

(+edit 2099 changed to 9999, please use 4 digit years!}
0
 

Author Comment

by:sam2929
ID: 39615959
Hi,

I know what to do please help me with first step and then we will move step 2 in below case for position_tcd  '10069587','10070988' i only want 1007988 record because it falls in the range of 01-Jan-2010 - 3 year .As it falls between that range i want start date as January, 01 2010.

Result what i am getting is


10070988       No Holder       May, 01 2008 00:00:00+0000       December, 31 9999 00:00:00+0000
10070988       No Holder       July, 02 2007 00:00:00+0000       April, 30 2008 00:00:00+0000

i need this to be as below how can i change start date to January, 01 2010 in below query?

10070988       No Holder       January, 01 2010 00:00:00+0000       December, 31 9999 00:00:00+0000



SELECT
POSITION_TCD,
'No Holder' POSITION_HOLDER,
POS_START_DT,
POS_END_DATE AS EMP_ASSIGN_END_DT
FROM  S_POSITION
where position_tcd not in (select distinct POSITION_TCD from S_EMP_ASSIGN)
and ADD_MONTHS(TO_CHAR('01-Jan-2010'),-36) < POS_START_DT
and POSITION_TCD IN ('10084336','10069587','10070988') 

used your sql fiddle below to get above results:

CREATE TABLE S_EMP_ASSIGN
	(POSITION_TCD int, POSITION_HOLDER int, EMP_ASSIGN_START_DT timestamp, EMP_ASSIGN_END_DT timestamp)
;

INSERT ALL 
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10009198, 108695, '29-Apr-2004 12:00:00 AM', '25-Mar-2009 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10080191, 107449, '08-Sep-2009 12:00:00 AM', '30-Jun-2010 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10080191, 109571, '05-Jan-2009 12:00:00 AM', '07-Sep-2009 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10083103, 133780, '10-May-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_EMP_ASSIGN (POSITION_TCD, POSITION_HOLDER, EMP_ASSIGN_START_DT, EMP_ASSIGN_END_DT)
		 VALUES (10084336, 101267, '08-Mar-2010 12:00:00 AM', '20-Mar-2011 12:00:00 AM')
SELECT * FROM dual
;

CREATE TABLE S_POSITION
	(POSITION_TCD int, POS_START_DT timestamp, POS_END_DATE timestamp) 
;

INSERT ALL 
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10009198, '01-Jan-1950 12:00:00 AM', '25-Mar-2009 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10069587, '05-Dec-2006 12:00:00 AM', '21-Jul-2008 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10070988, '02-Jul-2007 12:00:00 AM', '30-Apr-2008 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10070988, '01-May-2008 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10080191, '05-Jan-2009 12:00:00 AM', '30-Jun-2010 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10083103, '19-Oct-2009 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
	INTO S_POSITION (POSITION_TCD, POS_START_DT, POS_END_DATE)
		 VALUES (10084336, '08-Feb-2010 12:00:00 AM', '28-Mar-2011 12:00:00 AM')
SELECT * FROM dual
;

Open in new window

http://sqlfiddle.com/#!4/878e7/7
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616001
I recommend you to stay with the question and not divert into side issues, plus I have already shown a method for getting 2010-01-01 into a column.

You provided a set of sample data (thanks) and an expected result; And I can already get close to that expected result. What you need to do is explain the anomalies - with words
YOURS:
      POSITION     POSEARLTST     POSLATEEND           EXPECTST     EXPECTEND     INS_EMP_ASSIGN
      10009198     1950-01-01     2009-03-25           2010-01-01   2009-03-25*              Yes
      10069587     2006-12-05     2008-07-21                     *            *              No 
      10070988     2008-05-01     9999-12-31           2010-01-01   9999-12-31               No
      10080191     2009-01-05     2010-06-30           2009-01-05*  2010-06-30               Yes
      10083103     2009-10-19     9999-12-31           2010-01-01   9999-12-31               Yes
      10084336     2010-02-08     2011-03-28           2010-02-08   2011-03-28*              Yes

MINE:
| POSITION_TCD | POS_EARLY_ST | POS_LATE_END | EXPECT_ST_GREATEST | EXPECT_END | IN_S_EMD_ASSIGN |
|--------------|--------------|--------------|--------------------|------------|-----------------|
|     10009198 |   1950-01-01 |   2009-03-25 |         2010-01-01 | 2013-11-01*|             Yes |
|     10069587 |   2006-12-05 |   2008-07-21 |         2010-01-01*| 2013-11-01*|              No |
|     10070988 |   2008-05-01 |   2099-12-31 |         2010-01-01 | 2099-12-31 |              No |
|     10080191 |   2009-01-05 |   2010-06-30 |         2010-01-01*| 2013-11-01 |             Yes |
|     10083103 |   2009-10-19 |   9999-12-31 |         2010-01-01 | 9999-12-31 |             Yes |
|     10084336 |   2010-02-08 |   2011-03-28 |         2010-02-08 | 2013-11-01*|             Yes |

Open in new window

There are 5 differences only, very simple, just explain why your result needs to be different to mine where you see an *

{+edit, by the way ignore 2099 it should have been 9999, but as you only provided 2 digit dates I was guessing at the time}
0
 

Author Comment

by:sam2929
ID: 39616003
We need union all for 4 different situation if you can help with me with above case then i will discuss case 2 so lets please do step by step

so please let me know how can i get below results:

Result what i am getting is


10070988       No Holder       May, 01 2008 00:00:00+0000       December, 31 9999 00:00:00+0000
10070988       No Holder       July, 02 2007 00:00:00+0000       April, 30 2008 00:00:00+0000

i need this to be as below how can i change start date to January, 01 2010 in below query?

10070988       No Holder       January, 01 2010 00:00:00+0000       December, 31 9999 00:00:00+0000
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616005
It's your question, but it's up to us to design a proposed solution
- and I haven't decided you need a union at all so far.

What is needed is: to describe the 5 differences. Please.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616009
Wait, you have changed the requirements again.....

what is this "No Holder" ????

In addition to describing the 5 differences also explain this "no holder"

Then amend the expected results so that AL columns are present.

---- my result for position 10070988 is:

| POSITION_TCD | POS_EARLY_ST | POS_LATE_END | EXPECT_ST_GREATEST | EXPECT_END | IN_S_EMD_ASSIGN |
|--------------|--------------|--------------|--------------------|------------|-----------------|
|     10070988 |   2008-05-01 |   9999-12-31 |         2010-01-01 | 9999-12-31 |              No |

Open in new window

0
 

Author Comment

by:sam2929
ID: 39616011
It is more then that 5 difference solution as we joining these two tables to different dimensions to flag some 'No Holder' records thats why i am saying if you can help me case by case i can get to solution quick
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616013
no, not quick, and not productive either.

I have given you the solution for that row, take a look, it matches the expected results.
      10070988     2008-05-01     9999-12-31           2010-01-01   9999-12-31                No
|     10070988 |   2008-05-01 |   9999-12-31 |         2010-01-01 | 9999-12-31 |              No |

Open in new window

0
 

Author Comment

by:sam2929
ID: 39616018
Problem with that query is cross join and left join and i don't understand that :( sorry .
That's why i am trying  to break this query in small pieces so that i can understand better what we doing
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616030
you can ignore the cross join - that is purely a "convenience" for now - my query isn't complete and iI put it there for my convenience.

"left join" is easy to explain, you have some records in s_position, but no matching record in s_emp_assign. A "left join" allows unmatched records to be listed

WITH LEFT JOIN
S_POSITION  S_EMP_ASSIGN
10009198    10009198
10069587    no
10070988    no
10070988    no
10080191    10080191
10083103    10083103
10084336    10084336

but with a normal "inner join" only rows that match will be listed.

WITH INNER JOIN
S_POSITION  S_EMP_ASSIGN
10009198    10009198
10080191    10080191
10083103    10083103
10084336    10084336


You do NEED a "left join" here.

just ignore the cross join.

Now can you you describe the 5 differences please.
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
ID: 39616047
and, here is a version of the query without the cross join and some comments.
SELECT
        p.position_tcd
     ,  to_char(p.pos_start_dt,'YYYY-MM-DD') pos_early_st
     ,  to_char(max(p.pos_end_date),'YYYY-MM-DD') pos_late_end
     ,  to_char(greatest(p.less3_dt,min(p.pos_start_dt)),'YYYY-MM-DD') AS expect_st_greatest
     ,  to_char(greatest(sysdate,max(p.pos_end_date)),'YYYY-MM-DD')  AS expect_end
     , CASE WHEN a.position_tcd IS NULL THEN 'No' ELSE 'Yes' END     AS in_s_emd_assign

FROM (
      /* to arrive at the column POS_EARLY_ST we only want
         "the most recent" record from s_position
         this is achieved by using row_number()
         which will assign the number 1 for each position
         with the most recent pos_start_dt
          & that is determined by the "ORDER BY pos_start_dt DESC"
      */
  
      SELECT
              position_tcd
            , pos_start_dt
            , pos_end_date
            , row_number() over (partition BY position_tcd
                                 ORDER BY pos_start_dt DESC ) AS rn
            , (SELECT to_date(to_char(EXTRACT (YEAR FROM sysdate)-3) || '-01-01','YYYY-MM-DD') from dual) as less3_dt
      FROM s_position
     ) p

/* the left join is NEEDED as it allows all positions in s_position to be listed
   but still match on s_emp_assign when that table does have a match
*/
LEFT JOIN s_emp_assign a ON p.position_tcd = a.position_tcd

WHERE p.rn = 1
AND p.position_tcd IN ( 10009198,10069587,10070988,10080191,10083103,10084336 )


GROUP BY
        p.position_tcd
     ,  p.pos_start_dt
     ,  p.less3_dt
     , CASE WHEN a.position_tcd IS NULL THEN 'No' ELSE 'Yes' END
ORDER BY
       p.position_tcd
;

Open in new window

now please try to concentrate on the logic of the requirements. We will interpret those and propose a solution - once a working solution is available: THEN it is worth understanding how we did it.
0
 

Author Comment

by:sam2929
ID: 39616060
Please see if below three make sense also ouput has changed for case 3:

Case 1 & 2 Record exist only in S_POSITION and not in S_EMP_ASSIGN so we hard code 'No Holder'


Case 1 10069587 : Filter out this record  as Dec 5, 2006 is less then Jan 1, 2010 - 3 year so we need to ignore this record


Case 2 10070988:

Should be final in result as May 1, 2008  is less then Jan 1, 2010 - 3 year

Result should be:

position_tcd |POSITION_HOLDER|pos_start_dt|pos_end_date
|     10070988 |   'No Holder' |  Jan 1, 2010 | Dec 31 9999

In above two cases record is only in S_POSITION and not in S_EMP_ASSIGN so we hard code 'No Holder'


Case 3

10084336: Exist in both tables

S_EMP_ASSIGN:

10084336      101267      10-03-08      11-03-20

AND

S_POSITION:

10084336      10-02-08      11-03-28

Result should be:
We have start date March 8,2010 because March 8, 2010  is less then Jan 1, 2010 - 3 year

position_tcd |POSITION_HOLDER|pos_start_dt|pos_end_date
| 10084336 |   'No Holder' |  March 8, 2010 | March 20,2011   ---This row comes from S_POSITION
| 10084336 |   101267 |   March 21,2011 |March 28,2011 --This row will be continue from S_EMP_ASSIGN and also positionholder in this case will be 101267
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616065
problem:
Case 1 10069587 : Filter out this record  as Dec 5, 2006 is less then Jan 1, 2010

Case 2 10070988:
Should be final in result as May 1, 2008  is less then Jan 1, 2010 - 3 year

same condition (less than 2010-01-01) but one is excluded, the other included

why?

select
  position_tcd
, pos_start_dt
from s_position
where pos_start_dt < to_date('2010-01-01','YYYY-MM-DD')

| POSITION_TCD |                    POS_START_DT |
|--------------|---------------------------------|
|     10009198 |  January, 01 1950 00:00:00+0000 |
|     10069587 | December, 05 2006 00:00:00+0000 |
|     10070988 |     July, 02 2007 00:00:00+0000 |
|     10070988 |      May, 01 2008 00:00:00+0000 |
|     10080191 |  January, 05 2009 00:00:00+0000 |
|     10083103 |  October, 19 2009 00:00:00+0000 |

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39616078
I give in....

This truly is frustrating. Yo are now a million miles away from the original question and I have just been wasting my time.

When you have your requirements firmly established, and the sample data is provided, with complete expected results, somebody else may take over from there.


position_tcd |POSITION_HOLDER|pos_start_dt|pos_end_date
| 10084336 |   'No Holder' |  March 8, 2010 | March 20,2011 | note 1
| 10084336 |   101267        | March 21,2011 |March 28,2011 | note 2

where does 101267 come from?

Note 1   --This row comes from S_POSITION
so, it exists in BOTH tables, but for some unexplained reason it becomes 'No Holder'
>> explain the reason

Note 2 -- This row will be continue from S_EMP_ASSIGN and also positionholder in this case will be 101267
>> this is a substantial expansion of the quetion, so everything I have done so far is wasted.

You have EXPANDED SCOPE.

Spend a great deal more time on getting the question properly defined please.

Bye.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618089
@sam2929
I got a little frustrated yesterday, sorry.

I'd like to suggest some tips for expressing requirements, and I will use your cases 1 & 2 as an example:
Case 1 10069587 : Filter out this record  as Dec 5, 2006 is less then Jan 1, 2010 - 3 year so we need to ignore this record
Case 2 10070988: Should be final in result as May 1, 2008  is less then Jan 1, 2010 - 3 year
TIP 1: replace "values" with "field names"
so in the above we have 2 date values "Dec 5, 2006" and "May 1, 2008"
replace those with [pos_start_dt]

also you have used " Jan 1, 2010 - 3 year" which is AMBIGUOUS. Jan 1, 2010 is already minus 3 years so use 2013 instead. Also it is also a value and we want a name instead, just place it inside brackets so it looks like [ Jan 1, 2013 - 3 year]

TIP 2: replace words like "equals" "less than" "greater than" with operator symbols
now we have something that looks like a mathematical "expression"

So, it would now look like this
Case 1 10069587 : Filter out this record  as [pos_start_dt] < [Jan 1, 2013 - 3 year] so we need to ignore this record
Case 2 10070988: Should be final in result as [pos_start_dt]  < [Jan 1, 2013 - 3 year]
TIP 3: Start with IF, then the "expression", then words
Case 1 10069587 : IF [pos_start_dt] < [Jan 1, 2013 - 3 year] Filter out this record  as so we need to ignore this record
Case 2 10070988:  IF [pos_start_dt] < [Jan 1, 2013 - 3 year] Should be final in result as
TIP 4: Be brief with words, don't repeat, remove stuff that doesn't add value.
 
For example: in "so we need to ignore this record"; remove "we need to"

After making these changes, this is what cases 1 & 2 look like:
Case 1 10069587 : IF [pos_start_dt] < [Jan 1, 2013 - 3 year] so ignore this record
Case 2 10070988:  IF [pos_start_dt] < [Jan 1, 2013 - 3 year] Should be in final result
Now, I hope by this example you can now see that satisfying both cases 1 & 2 is currently impossible. You cannot exclude records and include records using the same "expression". So, the options are
1. the requirements are wrong, OR
2. there are missing additional parts to the requirements

E.G
    Case 1 10069587 : IF [pos_start_dt]  <  [Jan 1, 2013 - 6 year] ignore this record
    Case 2 10070988 : IF [pos_start_dt] >= [Jan 1, 2013 - 6 year] INCLUDE this record

    examples:
    where [Jan 1, 2013 - 6 year] = Jan 1, 2007

    Case 1 10069587 :  Dec 5, 2006  < Jan 1, 2007  ; ignore
    Case 2 10070988 : May 1, 2008 >= Jan 1, 2007  ; INCLUDE
0
 

Author Comment

by:sam2929
ID: 39618163
i think i got the solution i need one help

Currently i do ADD_MONTHS(to_date('01/01/2010','dd/mm/yyyyy'),-36) can i make this dynamic like  current_date - 3 year but always start first month of Jan

 
select POSITION_TCD,MAX(POSITION_HOLDER) POSITION_HOLDER,MAX(POS_START_DT) POS_START_DT,MIN(POS_END_DATE) POS_END_DATE
from
    (SELECT
    POSITION_TCD,
    'No Holder' POSITION_HOLDER,
    CASE WHEN
    POS_START_DT > ADD_MONTHS(to_date('01/01/2010','dd/mm/yyyy'),-36) then '01/01/2010'
    END
     AS POS_START_DT
     ,
     POS_END_DATE
    FROM S_POSITION
    where position_tcd not in (select distinct POSITION_TCD from S_EMP_ASSIGN)
    and ADD_MONTHS(to_date('01/01/2010','dd/mm/yyyy'),-36) < POS_START_DT) t
group by t.POSITION_TCD
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618171
I believe I have discovered the heart and soul of the problem here.

This is in your question
the definition of 3 years is Jan 1 of the current year minus 3 years. So for Oct 30, 2013, the starting point of the vacancy calendar is Jan 1, 2010.
but what you actually want is:
So for Oct 30, 2013, the starting point of the vacancy calendar is Jan 1, 2007.

That is SIX YEARS! from the current year.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618174
Cases 1 & 2 using SIX YEARS
| POSITION_TCD | IN_S_EMD_ASSIGN | EXPECT_ST_GREATEST | EXPECT_END | POS_EARLY_ST | POS_LATE_END |
|--------------|-----------------|--------------------|------------|--------------|--------------|
|     10070988 |       No Holder |         2010-01-01 | 9999-12-31 |   2008-05-01 |   9999-12-31 |

Open in new window

produced by:
SELECT
       p.position_tcd
     , CASE WHEN a.position_tcd IS NULL THEN 'No Holder' ELSE 'Yes' END     AS in_s_emd_assign
     , to_char(greatest(less3.dt,min(p.pos_start_dt)),'YYYY-MM-DD')         AS expect_st_greatest
     , to_char(greatest(sysdate,max(p.pos_end_date)),'YYYY-MM-DD')          AS expect_end
     , to_char(max(p.pos_start_dt),'YYYY-MM-DD')                                pos_early_st
     , to_char(max(p.pos_end_date),'YYYY-MM-DD')                                pos_late_end

FROM s_position p

LEFT JOIN s_emp_assign a ON p.position_tcd = a.position_tcd


/* PLEASE don't worry about "cross join" it is useful here */
CROSS JOIN (SELECT to_date(to_char(EXTRACT (YEAR FROM sysdate)-3) || '-01-01','YYYY-MM-DD') as dt from dual) less3

WHERE p.position_tcd IN ( 10069587,10070988 )
AND p.pos_start_dt > (SELECT to_date(to_char(EXTRACT (YEAR FROM sysdate)-6) || '-01-01','YYYY-MM-DD') as dt from dual)

GROUP BY
        p.position_tcd
     ,  less3.dt
     , CASE WHEN a.position_tcd IS NULL THEN 'No Holder' ELSE 'Yes' END
ORDER BY
       p.position_tcd
	

Open in new window

Please don't worry about the cross join!!!! It is very useful here. When we get to the end and you still don't understand I'll explain.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39618177
I sort of have to agree with PortletPaul, you seem to be all over the place with your requirements.

I still have not seen a well defined requirement.

I'm not even going to try to go back through this question and try to decipher the requirement.

I can only go what what you say you want right now.

>>can i make this dynamic like  current_date - 3 year but always start first month of Jan

To take the current date and roll it back to January 1st:  trunc(sysdate,'YY')

The to subtract 3 years from it:
add_months(trunc(sysdate,'YY'),-36)

That returns:  01/01/2010

Is this what you are after?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618180
This is something you should not do in your query, it is NOT efficient:
   WHERE position_tcd NOT IN
                              (SELECT DISTINCT POSITION_TCD
                              FROM S_EMP_ASSIGN
                              )

Open in new window

Using a LEFT JOIN is more efficient, I'll explain more when we get toward the end.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39618190
the "secret password" here is that in reality there are 2 reference dates

a. for filtering the data, it is current year - 6 years
b. for output, it is current year - 3 years

for a.
I have proposed:
to_date(to_char(EXTRACT (YEAR FROM sysdate)-6) || '-01-01','YYYY-MM-DD')

as I was trying to use year as the unit, but it could of course be

add_months(trunc(sysdate,'YYYY'),-72)

so as that is shorter I'll follow this from here.
for b. just change from -6 years to -3 or from -72 months to -36

ps: I am trying to encourage sam2929 to always use YYYY
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39618234
I've tidied-up a bit and applied the add_months approach, result for cases 1 & 2:
| POSITION_TCD | IN_S_EMD_ASSIGN |  EXPECT_ST | EXPECT_END | POS_EARLY_ST | POS_LATE_END |
|--------------|-----------------|------------|------------|--------------|--------------|
|     10070988 |       No Holder | 2010-01-01 | 9999-12-31 |   2008-05-01 |   9999-12-31 |

Open in new window

produced by
SELECT
       p.position_tcd
     , CASE WHEN a.position_tcd IS NULL THEN 'No Holder' ELSE 'Yes' END AS in_s_emd_assign
     , to_char(greatest(less3.dt,min(p.pos_start_dt)),'YYYY-MM-DD')     AS expect_st
     , to_char(greatest(sysdate,max(p.pos_end_date)),'YYYY-MM-DD')      AS expect_end
     , to_char(max(p.pos_start_dt),'YYYY-MM-DD')                        AS pos_early_st
     , to_char(max(p.pos_end_date),'YYYY-MM-DD')                        AS pos_late_end

FROM s_position p

LEFT JOIN s_emp_assign a ON p.position_tcd = a.position_tcd


/* PLEASE dont worry about "cross join" it is useful here */
CROSS JOIN (SELECT add_months(trunc(sysdate,'YYYY'),-36) as dt from dual) less3

WHERE p.position_tcd IN ( 10069587,10070988 )
AND p.pos_start_dt > (SELECT add_months(trunc(sysdate,'YYYY'),-72) from dual)

GROUP BY
       p.position_tcd
     , less3.dt
     , CASE WHEN a.position_tcd IS NULL THEN 'No Holder' ELSE 'Yes' END
ORDER BY
       p.position_tcd
;

-- http://sqlfiddle.com/#!4/8150a/49 

Open in new window

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

Suggested Solutions

Title # Comments Views Activity
Number Format 1 45
Need help with Oracle syntax 4 42
Oracle SQL Select within a Where Clause 9 42
sql for Oracle views 8 38
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

14 Experts available now in Live!

Get 1:1 Help Now