• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

getting history from tables

Hi,
I have two source table:

S_JOB_PAY_SCALE

JOB_TCD       TYPE_TCD  JPS_START_DT      JPS_END_DATE PAY_LEVEL_MIN PAY_LEVEL_MAX                        
158           02           2002-11-10        2005-03-21    1               4


Then i have another table S_PAY_SCALE

TYPE_TCD      START_DT        END_DT               LEVEL            WAGE_TYPE         RATE

02             2002-11-10      2003-03-31      1              1006       23.984                        
02               2003-04-01      2004-03-31      1            1006       24.704                        
02              2004-04-01      2005-03-31      1               1006       27.432

02             2002-11-10      2003-03-31      4              1006       33.984                        
02               2003-04-01      2004-03-31      4              1006       44.704                        
02              2004-04-01      2005-03-31      4               1006       57.432


Based upon TYPE_TCD,LEVEL and START_DT
and END_DT falls between JPS_START_DT      JPS_END_DATE

Final output will be:

D_JOB                                                                              
JOB_TCD           TYPE_TCD      JPS_START_DT      JPS_END_DATE  LEVEL_MIN      LEVEL_MAX WAGE_TYPE  RATE_MIN      RATE_MAX

158            02      2002-11-10      2003-03-31      1         4         1006           23.984        33.984
158            02      2003-04-01      2004-03-31      1         4         1006           24.704        44.704
158            02      2004-04-01      2005-03-21      1         4         1006           27.432        57.432

Idea is to keep history in final output based upon S_PAY_SCALE dates and also
merge the pay_rate in one line.
0
sam2929
Asked:
sam2929
  • 2
1 Solution
 
MikeOM_DBACommented:
Just Join the tables.
Show us what you got.
0
 
sam2929Author Commented:
Here is what i have did so far i am getting three rows  for rate_min which is ok but i need
to get rate_max too who can i do that?

SELECT
A.JOB_TCD,
A.PAY_SCALE_AREA_TCD,
A.PAY_SCALE_TYPE_TCD,
A.PAY_GROUP_MIN,
A.PAY_LEVEL_MIN,
A.PAY_GROUP_MAX,
A.PAY_LEVEL_MAX,
A.Jps_Start_Dt,
A.Jps_End_Dt ,
C.Pay_Group,
C.Pay_Level,
C.PAY_RATE
FROM HRPRSTAGEADM.S_JOB_PAY_SCALE A
inner Join S_Pay_Scale C
On A.Pay_Scale_Area_Tcd =c.Pay_Scale_Area_Tcd
And A.Pay_Scale_Type_Tcd =C.Pay_Scale_Type_Tcd
And (A.Pay_Group_Min = C.Pay_Group Or  A.Pay_Group_Max=C.Pay_Group)
And A.Pay_Level_Min = C.Pay_Level
--or  A.Pay_Level_Max = C.Pay_Level)
Where A.Job_Tcd = '10002427'
And  C.Pay_Scale_Start_Dt >= A.Jps_Start_Dt And  C.Pay_Scale_Start_Dt<= A.Jps_End_Dt
0
 
MikeOM_DBACommented:
Now group it like this:
  SELECT j.job_tcd, j.type_tcd, p.start_dt
       , p.end_dt, j.pay_level_min, j.pay_level_max
       , MIN ( p.lvl) level_min, MAX ( p.lvl) level_max, p.wage_type
       , MIN ( p.rate) rate_min, MAX ( p.rate) rate_max
    FROM s_job_pay_scale j, s_pay_scale p
   WHERE p.type_tcd = j.type_tcd
     AND (p.start_dt BETWEEN j.jps_start_dt AND j.jps_end_date)
     AND (p.end_dt BETWEEN j.jps_start_dt AND j.jps_end_date)
GROUP BY j.job_tcd
       , j.type_tcd
       , p.start_dt
       , p.end_dt
       , j.pay_level_min
       , j.pay_level_max
       , p.wage_type
/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now