Solved

Get data between date range from Master table

Posted on 2013-12-04
6
405 Views
Last Modified: 2013-12-05
S_EMP_ASSIGN looks like:

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        
10032216    110288    107418                Hold<>Subs         04-04-29    04-05-30      
10032263    110288    108635                Hold<>Subs          04-05-31    05-06-26      
10032229    110288    No Substantive    No Substantive    05-06-27    06-07-26        
99999999    110288    No Substantive    No Substantive    06-07-27    99-12-31      

D_EMPLOYEE:

PERSONNEL_NUM,EFFECTIVE_DATE,EXPIRY_DATE

110288    99-01-04    04-04-28
110288    04-04-29    04-05-30
110288    04-05-31    04-12-23
110288    04-12-24    05-06-26
110288    05-06-27    05-12-22
110288    05-12-23    06-12-21
110288    06-12-22    07-12-20
110288    07-12-21    08-12-18
110288    08-12-19    09-12-23
110288    09-12-24    10-12-22
110288    10-12-23    11-12-31
110288    12-01-01    12-12-31
110288    13-01-01    99-12-31


Final Results should be we match S_EMP_ASSIGN position_hoder with D_EMPLOYEE Personnel_num to get below results:

S_EMP_ASSIGN should be driving table :

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      
10032216    110288    107418                Hold<>Subs          04-04-29    04-05-30      
10032263    110288    108635                Hold<>Subs          04-05-31    04-12-23
10032263    110288    108635                Hold<>Subs          04-12-24    05-06-26  
10032229    110288    No Substantive    No Substantive    05-06-27    05-12-22  
10032229    110288    No Substantive    No Substantive    05-12-23    06-07-26 ----as S_EMP_ASSIGN end date is 05-06-27
99999999    110288    No Substantive    No Substantive    06-07-27    06-12-21  
99999999    110288    No Substantive    No Substantive    06-12-22    07-12-20  
99999999    110288    No Substantive    No Substantive    07-12-21    08-12-18
99999999    110288    No Substantive    No Substantive    08-12-19    09-12-23  
99999999    110288    No Substantive    No Substantive    09-12-24    10-12-22
99999999    110288    No Substantive    No Substantive    10-12-23    11-12-31  
99999999    110288    No Substantive    No Substantive    12-01-01    12-12-31
99999999    110288    No Substantive    No Substantive    13-01-01    99-12-31
0
Comment
Question by:sam2929
  • 4
  • 2
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39698170
I'm assuming most of your data is for the years 2000+

but, what does 99 mean?

1999 or 2099

in either case you have data that doesn't make sense.


if 1999 then

 06-07-27    99-12-31         this record ends before it starts

if 2099 then

99-01-04    04-04-27       this record expires before it becomes effective
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39698203
I made the assumption that if 99 was in the earlier column then it was 1999 and if 99 was in the later column then it was 2099
I also assumed your date values were actually dates or timestamps and not simply text with abiguous formatting

If both of those assumptions are correct, then this query returns the correct results


  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
    FROM s_emp_assign a, d_employee e
   WHERE a.position_holder = e.personnel_num
     AND a.emp_assign_start_dt <= e.expiry_date
     AND a.emp_assign_end_dt >= e.effective_date
ORDER BY emp_assign_start_dt
0
 

Author Comment

by:sam2929
ID: 39698652
Your assumption is right but they want to add one more table and do dates based on position_tcd from d_position

S_EMP_ASSIGN
Position TCD    Holder          Subs            Assign Type             Start Dt        End Dt
10005990        105608          105608          Hold=Subs               24-Oct-03       10-Dec-06
10005990        No Holder       105608          No Holder               11-Dec-06       11-Dec-06
10005990        120727  1       05608           Hold<>Subs              12-Dec-06       04-Mar-07
10005990        120727          No SubstantivNo Substantive     05-Mar-07       29-Apr-07
10005990        105976          105976          Hold=Subs               30-Apr-07       31-Dec-99

D_EMPLOYEE
Personnel Num   Start Dt        End Dt  Employee SK
105608  31-Aug-99       22-Oct-03       37184601
105608  23-Oct-03       23-Oct-03       37184602
105608  24-Oct-03       25-Dec-03       37184603
105608  26-Dec-03       31-Dec-03       37184604
105608  01-Jan-04       27-Jun-04       37183058
105608  28-Jun-04       28-Dec-04       37183059
105608  29-Dec-04       31-Dec-04       37183060
105608  01-Jan-05       30-Jun-05       37184605
105608  01-Jul-05       31-Dec-05       37183061
105608  01-Jan-06       28-Jun-06       37184606
105608  29-Jun-06       30-Jun-06       37184607
105608  01-Jul-06       06-Aug-06       37184608
105608  07-Aug-06       10-Dec-06       37183062
105608  11-Dec-06       31-Dec-06       37184609
105608  01-Jan-07       04-Mar-07       37183063
105608  05-Mar-07       30-Jun-07       37183064
105608  01-Jul-07       10-Dec-07       37183065
105608  11-Dec-07       31-Dec-07       37183066
105608  01-Jan-08       30-Jun-08       37183067
105608  01-Jul-08       10-Dec-08       37183068
105608  11-Dec-08       31-Dec-08       37183069
105608  01-Jan-09       30-Jun-09       37184610
105608  01-Jul-09       10-Dec-09       37184611
105608  11-Dec-09       31-Dec-09       37184612
105608  01-Jan-10       29-Mar-10       37183070
105608  30-Mar-10       20-Jun-10       37184613
105608  21-Jun-10       30-Jun-10       37184614
105608  01-Jul-10       31-Dec-10       37183071
105608  01-Jan-11       31-Dec-99       37183072
105976  05-Jan-04       31-Dec-04       37187729
105976  01-Jan-05       02-Jun-05       37187730
105976  03-Jun-05       30-Jun-05       37187731
105976  01-Jul-05       31-Dec-05       37190297
105976  01-Jan-06       30-Jun-06       37187732
105976  01-Jul-06       02-Dec-06       37187733
105976  03-Dec-06       31-Dec-06       37187734
105976  01-Jan-07       21-Jan-07       37190298
105976  22-Jan-07       29-Apr-07       37187735
105976  30-Apr-07       30-Jun-07       37187736
105976  01-Jul-07       31-Dec-07       37190299
105976  01-Jan-08       18-Jan-08       37190300
105976  19-Jan-08       30-Jun-08       37187737
105976  01-Jul-08       31-Dec-08       37190301
105976  01-Jan-09       18-Jan-09       37187738
105976  19-Jan-09       30-Jun-09       37187739
105976  01-Jul-09       31-Dec-09       37187740
105976  01-Jan-10       18-Jan-10       37190302
105976  19-Jan-10       30-Jun-10       37187741
105976  01-Jul-10       29-Aug-10       37190303
105976  30-Aug-10       31-Dec-10       37187742
105976  01-Jan-11       13-Feb-11       37190304
105976  14-Feb-11       31-Dec-99       37187743
120727  19-Dec-05       31-Dec-05       37360436
120727  01-Jan-06       30-Jun-06       37360437
120727  01-Jul-06       03-Sep-06       37360438
120727  04-Sep-06       11-Dec-06       37365101
120727  12-Dec-06       31-Dec-06       37365102
120727  01-Jan-07       29-Apr-07       37365103
120727  30-Apr-07       30-Jun-07       37360439
120727  01-Jul-07       31-Dec-07       37365104
120727  01-Jan-08       11-May-08       37365105
120727  12-May-08       30-Jun-08       37365106
120727  01-Jul-08       03-Aug-08       37365107
120727  04-Aug-08       28-Sep-08       37365108
120727  29-Sep-08       31-Dec-08       37365109
120727  01-Jan-09       30-Jun-09       37360440
120727  01-Jul-09       11-Nov-09       37360441
120727  12-Nov-09       31-Dec-09       37360442
120727  01-Jan-10       30-Jun-10       37365110
120727  01-Jul-10       29-Aug-10       37365111
120727  30-Aug-10       31-Dec-10       37360443
120727  01-Jan-11       11-May-11       37365112
120727  12-May-11       31-Dec-99       37360444

D_POSITION
Position TCD    Start Dt        End Dt          Position SK
10005990        01-Jan-50       31-May-03       11612740
10005990        01-Jun-03       06-Jun-05       11612741
10005990        07-Jun-05       07-Dec-05       11611731
10005990        08-Dec-05       02-Jul-06       11611732
10005990        03-Jul-06       27-Aug-06       11611733
10005990        28-Aug-06       16-Jan-11       11611734
10005990        17-Jan-11       31-Dec-99       11612742

Expected Results for F_EMP_ASSIGN
Employee TCD    Employee SK     Position TCD    Position SK     Assign Name     Assign Type     Start Dt        End Dt
105608  37184603        10005990        11612741        HOLDER  Hold=Subs       24-Oct-03       25-Dec-03
105608  37184603        10005990        11612741        SUBSTANTIVE     Hold=Subs       24-Oct-03       25-Dec-03
105608  37184604        10005990        11612741        HOLDER  Hold=Subs       26-Dec-03       31-Dec-03
105608  37184604        10005990        11612741        SUBSTANTIVE     Hold=Subs       26-Dec-03       31-Dec-03
105608  37183058        10005990        11612741        HOLDER  Hold=Subs       01-Jan-04       27-Jun-04
105608  37183058        10005990        11612741        SUBSTANTIVE     Hold=Subs       01-Jan-04       27-Jun-04
105608  37183059        10005990        11612741        HOLDER  Hold=Subs       28-Jun-04       28-Dec-04
105608  37183059        10005990        11612741        SUBSTANTIVE     Hold=Subs       28-Jun-04       28-Dec-04
105608  37183060        10005990        11612741        HOLDER  Hold=Subs       29-Dec-04       31-Dec-04
105608  37183060        10005990        11612741        SUBSTANTIVE     Hold=Subs       29-Dec-04       31-Dec-04
105608  37184605        10005990        11612741        HOLDER  Hold=Subs       01-Jan-05       06-Jun-05
105608  37184605        10005990        11612741        SUBSTANTIVE     Hold=Subs       01-Jan-05       06-Jun-05
105608  37184605        10005990        11611731        HOLDER  Hold=Subs       07-Jun-05       30-Jun-05
105608  37184605        10005990        11611731        SUBSTANTIVE     Hold=Subs       07-Jun-05       30-Jun-05
105608  37183061        10005990        11611731        HOLDER  Hold=Subs       01-Jul-05       07-Dec-05
105608  37183061        10005990        11611731        SUBSTANTIVE     Hold=Subs       01-Jul-05       07-Dec-05
105608  37183061        10005990        11611732        HOLDER  Hold=Subs       08-Dec-05       31-Dec-05
105608  37183061        10005990        11611732        SUBSTANTIVE     Hold=Subs       08-Dec-05       31-Dec-05
105608  37184606        10005990        11611732        HOLDER  Hold=Subs       01-Jan-06       28-Jun-06
105608  37184606        10005990        11611732        SUBSTANTIVE     Hold=Subs       01-Jan-06       28-Jun-06
105608  37184607        10005990        11611732        HOLDER  Hold=Subs       29-Jun-06       30-Jun-06
105608  37184607        10005990        11611732        SUBSTANTIVE     Hold=Subs       29-Jun-06       30-Jun-06
105608  37184608        10005990        11611732        HOLDER  Hold=Subs       01-Jul-06       02-Jul-06
105608  37184608        10005990        11611732        SUBSTANTIVE     Hold=Subs       01-Jul-06       02-Jul-06
105608  37184608        10005990        11611733        HOLDER  Hold=Subs       03-Jul-06       06-Aug-06
105608  37184608        10005990        11611733        SUBSTANTIVE     Hold=Subs       03-Jul-06       06-Aug-06
105608  37183062        10005990        11611733        HOLDER  Hold=Subs       07-Aug-06       27-Aug-06
105608  37183062        10005990        11611733        SUBSTANTIVE     Hold=Subs       07-Aug-06       27-Aug-06
105608  37183062        10005990        11611734        HOLDER  Hold=Subs       28-Aug-06       10-Dec-06
105608  37183062        10005990        11611734        SUBSTANTIVE     Hold=Subs       28-Aug-06       10-Dec-06
105608  37184609        10005990        11611734        SUBSTANTIVE     No Holder       11-Dec-06       11-Dec-06
120727  37365102        10005990        11611734        HOLDER  Hold<>Subs      12-Dec-06       31-Dec-06
105608  37184609        10005990        11611734        SUBSTANTIVE     Hold<>Subs      12-Dec-06       31-Dec-06
120727  37365103        10005990        11611734        HOLDER  Hold<>Subs      01-Jan-07       04-Mar-07
105608  37183063        10005990        11611734        SUBSTANTIVE     Hold<>Subs      01-Jan-07       04-Mar-07
120727  37365103        10005990        11611734        HOLDER  No Substantive  05-Mar-07       29-Apr-07
105976  37187736        10005990        11611734        HOLDER  Hold=Subs       30-Apr-07       30-Jun-07
105976  37187736        10005990        11611734        SUBSTANTIVE     Hold=Subs       30-Apr-07       30-Jun-07
105976  37190299        10005990        11611734        HOLDER  Hold=Subs       01-Jul-07       31-Dec-07
105976  37190299        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jul-07       31-Dec-07
105976  37190300        10005990        11611734        HOLDER  Hold=Subs       01-Jan-08       18-Jan-08
105976  37190300        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jan-08       18-Jan-08
105976  37187737        10005990        11611734        HOLDER  Hold=Subs       19-Jan-08       30-Jun-08
105976  37187737        10005990        11611734        SUBSTANTIVE     Hold=Subs       19-Jan-08       30-Jun-08
105976  37190301        10005990        11611734        HOLDER  Hold=Subs       01-Jul-08       31-Dec-08
105976  37190301        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jul-08       31-Dec-08
105976  37187738        10005990        11611734        HOLDER  Hold=Subs       01-Jan-09       18-Jan-09
105976  37187738        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jan-09       18-Jan-09
105976  37187739        10005990        11611734        HOLDER  Hold=Subs       19-Jan-09       30-Jun-09
105976  37187739        10005990        11611734        SUBSTANTIVE     Hold=Subs       19-Jan-09       30-Jun-09
105976  37187740        10005990        11611734        HOLDER  Hold=Subs       01-Jul-09       31-Dec-09
105976  37187740        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jul-09       31-Dec-09
105976  37190302        10005990        11611734        HOLDER  Hold=Subs       01-Jan-10       18-Jan-10
105976  37190302        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jan-10       18-Jan-10
105976  37187741        10005990        11611734        HOLDER  Hold=Subs       19-Jan-10       30-Jun-10
105976  37187741        10005990        11611734        SUBSTANTIVE     Hold=Subs       19-Jan-10       30-Jun-10
105976  37190303        10005990        11611734        HOLDER  Hold=Subs       01-Jul-10       29-Aug-10
105976  37190303        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jul-10       29-Aug-10
105976  37187742        10005990        11611734        HOLDER  Hold=Subs       30-Aug-10       31-Dec-10
105976  37187742        10005990        11611734        SUBSTANTIVE     Hold=Subs       30-Aug-10       31-Dec-10
105976  37190304        10005990        11611734        HOLDER  Hold=Subs       01-Jan-11       16-Jan-11
105976  37190304        10005990        11611734        SUBSTANTIVE     Hold=Subs       01-Jan-11       16-Jan-11
105976  37190304        10005990        11612742        HOLDER  Hold=Subs       17-Jan-11       13-Feb-11
105976  37190304        10005990        11612742        SUBSTANTIVE     Hold=Subs       17-Jan-11       13-Feb-11
105976  37187743        10005990        11612742        HOLDER  Hold=Subs       14-Feb-11       31-Dec-99
105976  37187743        10005990        11612742        SUBSTANTIVE     Hold=Subs       14-Feb-11       31-Dec-99
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 73

Expert Comment

by:sdstuber
ID: 39698690
that's a new question with new requirements
0
 

Author Comment

by:sam2929
ID: 39698763
we can say addition of logic
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39698793
yes, the "addition" is a significant change to the requirements.

If what you asked and what you needed aren't the same thing, that's fine.  Simply close this question (unless there is more to pursue on it)
and open a new one with the new requirements, new data and new expected results.
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

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.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

744 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

11 Experts available now in Live!

Get 1:1 Help Now