?
Solved

date range from three table join

Posted on 2013-12-05
7
Medium Priority
?
248 Views
Last Modified: 2013-12-12
As the example is huge so i am attaching a doc for clarification.
Results-generation.doc
0
Comment
Question by:sam2929
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39699420
please post that as text, even better as insert statements.
screen captures aren't usable for building test cases

are you trying to get a query that joins first 3 tables and produces the 4th?

what are the join conditions between the 3 tables?


If so, where does the emp_assign_name come from?  It's not in any of the 3 source tables
0
 

Author Comment

by:sam2929
ID: 39699574
S_EMP_ASSIGN is driving table it is joined with position_holder or substantive_holder
to D_EMPLOYEE personnel_num and with date ranges to get results.

ALSO S_EMP_ASSIGN need to be joined with D_POSITION position_tcd to get POSITION_SK.

EMP_ASSIGN_NAME is derived as below:

IF S_EMP_ASSIGN_S.SUBSTANTIVE_HOLDER is not "No Substantive" THEN SUBSTANTIVE

IF S_EMP_ASSIGN_P.POSITION_HOLDER  is not "No Holder" THEN HOLDER



Query:

select POSITION_TCD,position_holder,substantive_holder,emp_assign_type
,emp_assign_start_dt,emp_assign_end_dt from S_EMP_ASSIGN
where (position_holder ='110288'or substantive_holder='110288')
where POSITION_tcd='10032216'

S_EMP_ASSIGN:

POSITION_TCD,position_holder,substantive_holder,emp_assign_type,emp_assign_start_dt,emp_assign_end_dt

99999999           110288            No Substantive      No Substantive      99-01-04      04-04-27
10031112            No Holder      110288                      No Holder            04-04-28      04-04-28
10032216           110288           107418                  Hold<>Subs        04-04-29      04-05-30
10031112            110246           110288                  Hold<>Subs        04-04-29      04-08-15
10032263           110288            108635                 Hold<>Subs        04-05-31      05-06-26
10031112            No Holder        110288                 No Holder            04-08-16      04-09-30
10031112            111196             110288                 Hold<>Subs         04-10-01      05-08-08
10032229           110288             No Substantive      No Substantive      05-06-27      06-07-26
10031112            No Holder        110288                  No Holder             05-08-09      05-09-04
10031112            118799            110288                  Hold<>Subs          05-09-05      05-09-09
10031112            No Holder        110288                  No Holder              05-09-10      05-10-16
10031112            120544            110288                  Hold<>Subs            05-10-17      06-07-26
99999999            110288            No Substantive      No Substantive         06-07-27      99-12-31

D_EMPLOYEE:

employee_sk,personnel_num,effective_date,expiry_date

37661352      110288      99-01-04      04-04-28
37668786      110288      04-04-29      04-05-30
37661353      110288      04-05-31      04-12-23
37668787      110288      04-12-24      05-06-26
37668788      110288      05-06-27      05-12-22
37661354      110288      05-12-23      06-12-21
37668789      110288      06-12-22      07-12-20
37668790      110288      07-12-21      08-12-18
37661355      110288      08-12-19      09-12-23
37661356      110288      09-12-24      10-12-22
37661357      110288      10-12-23      99-12-31

D_POSITION:

position_sk,position_tcd,effective_date,expiry_date

11722086            10031112            50-01-01      03-10-31
11722087            10031112            03-11-01      10-11-24
11722088            10031112            10-11-25      99-12-31
11724457            10032216      50-01-01      03-10-31
11724458            10032216      03-11-01      99-12-31
11724476            10032229      50-01-01      03-10-31
11724477            10032229      03-11-01      08-05-20
11723585            10032263      50-01-01      03-10-31
11723586            10032263      03-11-01      99-12-31
11768613            99999999      01-01-01      99-12-31


Final result should be:

EMPLOYEE_SK,PERSONNEL_NUM,POSITION_SK,POSITION_TCD,EMP_ASSIGN_NAME,EMP_ASSIGN_TYPE,EMP_ASSIGN_START_DT,EMP_ASSIGN_END_DT

37661352      110288      11768613            99999999      HOLDER            No Substantive      1/4/1999            4/27/2004
37661357      110288      11722087            10031112            SUBSTANTIVE      No Holder      4/28/2004      4/28/2004
37668786      110288      11724458            10032216      HOLDER            Hold<>Subs      2004-04-29      5/30/2004
37668786      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      2004-04-29      5/30/2004
37661353      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      5/31/2004      8/15/2004
37661353      110288      11723586            10032263      HOLDER            Hold<>Subs      5/31/2004      12/23/2004
37668787      110288      11723586            10032263      HOLDER            Hold<>Subs      12/24/2004      6/26/2005
37661353      110288      11722087            10031112            SUBSTANTIVE      No Holder      8/16/2004      9/30/2004
37661353      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      10/1/2004      12/23/2004
37668787      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      12/24/2004      6/26/2005
37668788      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      6/27/2005      8/8/2005
37668788      110288      11724477            10032229      HOLDER      No       Substantive      6/27/2005      12/22/2005
37661354      110288      11724477            10032229      HOLDER      No       Substantive      12/23/2005      7/26/2006
37668788      110288      11722087            10031112            SUBSTANTIVE      No Holder      8/9/2005            9/4/2005
37668788      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      9/5/2005            9/9/2005
37668788      110288      11722087            10031112            SUBSTANTIVE      No Holder      9/10/2005      10/16/2005
37668788      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      10/17/2005      12/22/2005
37661354      110288      11722087            10031112            SUBSTANTIVE      Hold<>Subs      12/23/2005      7/26/2006
37661354      110288      11768613            99999999      HOLDER            No Substantive      7/27/2006      12/21/2006
37668789      110288      11768613            99999999      HOLDER            No Substantive      12/22/2006      12/20/2007
37668790      110288      11768613            99999999      HOLDER            No Substantive      12/21/2007      12/18/2008
37661355      110288      11768613            99999999      HOLDER            No Substantive      12/19/2008      12/23/2009
37661356      110288      11768613            99999999      HOLDER            No Substantive      12/24/2009      12/22/2010
37661357      110288      11768613            99999999      HOLDER            No Substantive      12/23/2010      12/31/9999
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39706120
why did you close this question?  I haven't gotten around to answering it yet
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sam2929
ID: 39707315
i got the solution
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39707560
can you post what you did?
0
 

Author Comment

by:sam2929
ID: 39707624
SELECT T1.POSITION_HOLDER,T1.EMPLOYEE_SK,T1.POSITION_TCD,T2.position_Sk,
T1.EMP_ASSIGN_NAME,T1.EMP_ASSIGN_TYPE,
CASE WHEN T1.emp_assign_start_dt < T2.EFFECTIVE_DATE THEN T2.EFFECTIVE_DATE ELSE T1.emp_assign_start_dt END effective_date  ,
CASE WHEN T1.emp_assign_end_dt > T2.expiry_date THEN T2.expiry_date ELSE T1.emp_assign_end_dt  END EXPIRY_DATE,
T1.ACTING_END_DATE,
T1.ACTING_FLAG
FROM (SELECT
a.POSITION_TCD,
a.POSITION_HOLDER,
a.SUBSTANTIVE_HOLDER,
a.EMP_ASSIGN_TYPE,
GREATEST(a.emp_assign_start_dt, e.effective_date) emp_assign_start_dt,
LEAST(a.emp_assign_end_dt, e.expiry_date) emp_assign_end_dt,
a.ACTING_END_DATE,
a.ACTING_FLAG,
'HOLDER' as EMP_ASSIGN_NAME,
e.EMPLOYEE_SK EMPLOYEE_SK
FROM s_emp_assign a, d_employee e
WHERE a.POSITION_HOLDER = e.personnel_num
--AND a.emp_assign_start_dt between e.effective_date and e.expiry_date
AND a.emp_assign_start_dt <= e.expiry_date
AND a.emp_assign_end_dt >= e.effective_date
union all
SELECT
a.POSITION_TCD,
a.SUBSTANTIVE_HOLDER POSITION_HOLDER,
a.SUBSTANTIVE_HOLDER,
a.EMP_ASSIGN_TYPE,
GREATEST(a.emp_assign_start_dt, e.effective_date) emp_assign_start_dt,
LEAST(a.emp_assign_end_dt, e.expiry_date) emp_assign_end_dt,
a.ACTING_END_DATE,
a.ACTING_FLAG,
'SUBSTANTIVE' as EMP_ASSIGN_NAME,
e.EMPLOYEE_SK EMPLOYEE_SK
FROM s_emp_assign a, d_employee e
WHERE a.SUBSTANTIVE_HOLDER = e.personnel_num
--AND a.emp_assign_start_dt between e.effective_date and e.expiry_date
AND a.emp_assign_start_dt <= e.expiry_date
AND a.emp_assign_end_dt >= e.effective_date) T1 ,D_POSITION T2
WHERE T1.POSITION_TCD=T2.POSITION_TCD AND  
(T1.EMP_ASSIGN_START_DT BETWEEN T2.effective_date AND T2.EXPIRY_DATE OR
 T1.EMP_ASSIGN_END_DT   BETWEEN T2.effective_date AND T2.EXPIRY_DATE OR
(T1.EMP_ASSIGN_START_DT <=  T2.effective_date AND EMP_ASSIGN_END_DT >=  T2.EXPIRY_DATE)) ORDER BY POSITION_HOLDER,EFFECTIVE_DATE
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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

765 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