Solved

date range from three table join

Posted on 2013-12-05
7
246 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 500 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
Independent Software Vendors: 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!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

729 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