Solved

date range from three table join

Posted on 2013-12-05
7
242 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
  • 3
  • 3
7 Comments
 
LVL 73

Accepted Solution

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

Expert Comment

by:sdstuber
Comment Utility
why did you close this question?  I haven't gotten around to answering it yet
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.

 

Author Comment

by:sam2929
Comment Utility
i got the solution
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
can you post what you did?
0
 

Author Comment

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

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

772 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