sam2929
asked on
find max min and put in one line
Hi,
S_PAY_SCALE and S_JOB_PAY_SCALE and are joined on PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP, and PAY_LEVEL, but there are two (2) relationships between S_JOB and S_PAY_SCALE - one for the MIN and one for the MAX. so data should look like Target:
S_JOB_PAY_SCALE:
job_tcd, pay_scale_area_tcd, pay_scale_type_tcd, pay_group_min, pay_level_min, pay_group_max, pay_level_max
,jps_start_dt,jps_end_dt
10028059 UN 04 0106 P1 0106 03 03-01-01 03-12-31
10028059 UN 04 0106 P1 0106 02 04-01-01 04-12-31
10028059 UN 04 0106 P1 0106 01 05-01-01 09-04-27
10028059 UN 04 0106 P1 0106 01 09-04-28 99-12-31
S_PAY_SCALE:
select pay_group,pay_level,pay_sc
from s_pay_scale
pay_group,pay_level,pay_sc
0106 00 UN 04 0 04-12-24 05-12-22
0106 00 UN 04 0 06-12-22 07-12-20
0106 00 UN 04 0 08-12-19 09-12-23
0106 00 UN 04 0 10-12-23 99-12-31
0106 01 UN 04 15.487 03-01-01 03-12-31
0106 01 UN 04 16.714 04-01-01 04-12-23
0106 01 UN 04 18.075 04-12-24 05-12-22
0106 01 UN 04 18.617 05-12-23 06-12-21
0106 01 UN 04 19.176 06-12-22 07-12-20
0106 01 UN 04 19.799 07-12-21 08-12-18
0106 01 UN 04 20.294 08-12-19 09-12-23
0106 01 UN 04 20.801 09-12-24 10-12-22
0106 01 UN 04 21.373 10-12-23 99-12-31
0106 02 UN 04 16.227 03-01-01 03-12-31
0106 02 UN 04 17.549 04-01-01 04-12-23
0106 02 UN 04 18.075 04-12-24 04-12-31
0106 03 UN 04 17.038 03-01-01 03-12-31
0106 P1 UN 04 14.713 03-01-01 03-12-31
0106 P1 UN 04 15.878 04-01-01 04-12-23
0106 P1 UN 04 17.171 04-12-24 05-12-22
0106 P1 UN 04 17.686 05-12-23 06-12-21
0106 P1 UN 04 18.217 06-12-22 07-12-20
0106 P1 UN 04 18.809 07-12-21 08-12-18
0106 P1 UN 04 19.279 08-12-19 09-12-23
0106 P1 UN 04 19.761 09-12-24 10-12-22
0106 P1 UN 04 20.304 10-12-23 99-12-31
0106 P2 UN 04 15.416 03-01-01 03-12-31
0106 P2 UN 04 16.672 04-01-01 04-12-23
0106 P2 UN 04 17.171 04-12-24 04-12-31
0106 P3 UN 04 16.186 03-01-01 03-12-31
Data should look like below:
Target:
D_JOB
10028059 UN 04 P1 P1 03-01-01 03-12-31 14.713 03 17.038
10028059 UN 04 P1 P1 04-01-01 04-12-23 15.878 02 17.549
10028059 UN 04 P1 P1 04-12-24 05-12-22 17.171 02 18.075
10028059 UN 04 P1 P1 05-12-23 06-12-21 17.686 01 18.617
10028059 UN 04 P1 P1 06-12-22 07-12-20 18.217 01 19.176
10028059 UN 04 P1 P1 07-12-21 08-12-18 18.809 01 19.799
10028059 UN 04 P1 P1 08-12-19 09-12-23 19.279 01 20.294
10028059 UN 04 P1 P1 03-01-01 03-12-31 19.761 01 20.801
10028059 UN 04 P1 P1 10-12-23 99-12-31 20.304 04 21.373
i am trying to do max min using below query but have limited sucess so need some help.
SELECT distinct
A.JOB_TCD,
C.Pay_Scale_Area_Tcd,
C.Pay_Scale_Type_Tcd,
--C.Pay_Group,
MIN(C.Pay_Level) As Pay_Level_Min,
MAX(c.Pay_Level)as Pay_Level_max,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt,
C.Pay_Scale_Area_Desc,
C.Pay_Scale_Type_Desc,
Min(Nvl(C.Pay_Rate,0)) As Pay_Rate_Min,
Max(Nvl(C.Pay_Rate,0)) As Pay_Rate_Max
,Min(Nvl(C.Pay_Wage_Type,'
,Max(NVL(C.Pay_Wage_Type,'
From Hrprstageadm.S_Job_Pay_Sca
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_Max = C.Pay_Group)
And (trim(A.Pay_Level_Max) = trim(C.Pay_Level) Or trim(A.Pay_Level_Min) = trim(C.Pay_Level))
And (C.Pay_Scale_Start_Dt Between A.Jps_Start_Dt And A.Jps_End_Dt Or C.Pay_Scale_End_Dt Between A.Jps_Start_Dt And A.Jps_End_Dt)
Where a.job_tcd ='10028059'
Group By
A.JOB_TCD,
C.Pay_Scale_Area_Tcd,
C.Pay_Scale_Type_Tcd,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt,
C.Pay_Scale_Area_Desc,
C.Pay_Scale_Type_Desc
S_PAY_SCALE and S_JOB_PAY_SCALE and are joined on PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP, and PAY_LEVEL, but there are two (2) relationships between S_JOB and S_PAY_SCALE - one for the MIN and one for the MAX. so data should look like Target:
S_JOB_PAY_SCALE:
job_tcd, pay_scale_area_tcd, pay_scale_type_tcd, pay_group_min, pay_level_min, pay_group_max, pay_level_max
,jps_start_dt,jps_end_dt
10028059 UN 04 0106 P1 0106 03 03-01-01 03-12-31
10028059 UN 04 0106 P1 0106 02 04-01-01 04-12-31
10028059 UN 04 0106 P1 0106 01 05-01-01 09-04-27
10028059 UN 04 0106 P1 0106 01 09-04-28 99-12-31
S_PAY_SCALE:
select pay_group,pay_level,pay_sc
from s_pay_scale
pay_group,pay_level,pay_sc
0106 00 UN 04 0 04-12-24 05-12-22
0106 00 UN 04 0 06-12-22 07-12-20
0106 00 UN 04 0 08-12-19 09-12-23
0106 00 UN 04 0 10-12-23 99-12-31
0106 01 UN 04 15.487 03-01-01 03-12-31
0106 01 UN 04 16.714 04-01-01 04-12-23
0106 01 UN 04 18.075 04-12-24 05-12-22
0106 01 UN 04 18.617 05-12-23 06-12-21
0106 01 UN 04 19.176 06-12-22 07-12-20
0106 01 UN 04 19.799 07-12-21 08-12-18
0106 01 UN 04 20.294 08-12-19 09-12-23
0106 01 UN 04 20.801 09-12-24 10-12-22
0106 01 UN 04 21.373 10-12-23 99-12-31
0106 02 UN 04 16.227 03-01-01 03-12-31
0106 02 UN 04 17.549 04-01-01 04-12-23
0106 02 UN 04 18.075 04-12-24 04-12-31
0106 03 UN 04 17.038 03-01-01 03-12-31
0106 P1 UN 04 14.713 03-01-01 03-12-31
0106 P1 UN 04 15.878 04-01-01 04-12-23
0106 P1 UN 04 17.171 04-12-24 05-12-22
0106 P1 UN 04 17.686 05-12-23 06-12-21
0106 P1 UN 04 18.217 06-12-22 07-12-20
0106 P1 UN 04 18.809 07-12-21 08-12-18
0106 P1 UN 04 19.279 08-12-19 09-12-23
0106 P1 UN 04 19.761 09-12-24 10-12-22
0106 P1 UN 04 20.304 10-12-23 99-12-31
0106 P2 UN 04 15.416 03-01-01 03-12-31
0106 P2 UN 04 16.672 04-01-01 04-12-23
0106 P2 UN 04 17.171 04-12-24 04-12-31
0106 P3 UN 04 16.186 03-01-01 03-12-31
Data should look like below:
Target:
D_JOB
10028059 UN 04 P1 P1 03-01-01 03-12-31 14.713 03 17.038
10028059 UN 04 P1 P1 04-01-01 04-12-23 15.878 02 17.549
10028059 UN 04 P1 P1 04-12-24 05-12-22 17.171 02 18.075
10028059 UN 04 P1 P1 05-12-23 06-12-21 17.686 01 18.617
10028059 UN 04 P1 P1 06-12-22 07-12-20 18.217 01 19.176
10028059 UN 04 P1 P1 07-12-21 08-12-18 18.809 01 19.799
10028059 UN 04 P1 P1 08-12-19 09-12-23 19.279 01 20.294
10028059 UN 04 P1 P1 03-01-01 03-12-31 19.761 01 20.801
10028059 UN 04 P1 P1 10-12-23 99-12-31 20.304 04 21.373
i am trying to do max min using below query but have limited sucess so need some help.
SELECT distinct
A.JOB_TCD,
C.Pay_Scale_Area_Tcd,
C.Pay_Scale_Type_Tcd,
--C.Pay_Group,
MIN(C.Pay_Level) As Pay_Level_Min,
MAX(c.Pay_Level)as Pay_Level_max,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt,
C.Pay_Scale_Area_Desc,
C.Pay_Scale_Type_Desc,
Min(Nvl(C.Pay_Rate,0)) As Pay_Rate_Min,
Max(Nvl(C.Pay_Rate,0)) As Pay_Rate_Max
,Min(Nvl(C.Pay_Wage_Type,'
,Max(NVL(C.Pay_Wage_Type,'
From Hrprstageadm.S_Job_Pay_Sca
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_Max = C.Pay_Group)
And (trim(A.Pay_Level_Max) = trim(C.Pay_Level) Or trim(A.Pay_Level_Min) = trim(C.Pay_Level))
And (C.Pay_Scale_Start_Dt Between A.Jps_Start_Dt And A.Jps_End_Dt Or C.Pay_Scale_End_Dt Between A.Jps_Start_Dt And A.Jps_End_Dt)
Where a.job_tcd ='10028059'
Group By
A.JOB_TCD,
C.Pay_Scale_Area_Tcd,
C.Pay_Scale_Type_Tcd,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt,
C.Pay_Scale_Area_Desc,
C.Pay_Scale_Type_Desc
This is untested, but I think you need to use a HAVING clause, and think the TRIM() function may be avoided. Could you try this please? Is it closer?
SELECT
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
--, C.Pay_Group
, MIN(C.Pay_Level) AS Pay_Level_Min
, MAX(c.Pay_Level) AS Pay_Level_max
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
, C.Pay_Scale_Area_Desc
, C.Pay_Scale_Type_Desc
, Min(Nvl(C.Pay_Rate, 0)) AS Pay_Rate_Min
, Max(Nvl(C.Pay_Rate, 0)) AS Pay_Rate_Max
, Min(Nvl(C.Pay_Wage_Type, 'No Data')) AS Pay_Wage_Min
, Max(NVL(C.Pay_Wage_Type, '-1')) AS Pay_Wage_Max
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_Max = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
WHERE a.job_tcd = '10028059'
GROUP BY
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
, C.Pay_Scale_Area_Desc
, C.Pay_Scale_Type_Desc
HAVING (
C.Pay_Level = MAX(c.Pay_Level)
OR C.Pay_Level = MIN(c.Pay_Level)
)
;ASKER
Adding having give below error
not a group by expression
not a group by expression
ah, yes of course sorry, unable to get to this until tomorrow.
In trying to use your sample data there are some fields absent
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
--C.Pay_Wage_Type
but I presume these don't influence the results.
The following does not match exactly your expected results e.g. there are 10 rows, but that extra row is still distinct. Please review this results and query:
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
--C.Pay_Wage_Type
but I presume these don't influence the results.
The following does not match exactly your expected results e.g. there are 10 rows, but that extra row is still distinct. Please review this results and query:
JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 |PAY_RATE_MIN |PAY_RATE_MAX | EXPECTED
----------|----|----|----|----|------------|------------|-------------|-------------|-----------------------|
10028059 | UN | 04 | 01 | P3 | 2001-03-01 | 2031-03-12 | 14.713 | 17.038 | 14.713 03 17.038
10028059 | UN | 04 | 01 | P2 | 2001-04-01 | 2023-04-12 | 15.878 | 17.549 | 15.878 02 17.549
10028059 | UN | 04 | 00 | P1 | 2024-04-12 | 2022-05-12 | 17.171 | 18.075 | 17.171 02 18.075
10028059 | UN | 04 | 02 | P2 | 2024-04-12 | 2031-04-12 | 17.171 | 18.075 | 17.171 02 18.075 << row is distinct
10028059 | UN | 04 | 01 | P1 | 2023-05-12 | 2021-06-12 | 17.686 | 18.617 | 17.686 01 18.617
10028059 | UN | 04 | 00 | P1 | 2022-06-12 | 2020-07-12 | 18.217 | 19.176 | 18.217 01 19.176
10028059 | UN | 04 | 01 | P1 | 2021-07-12 | 2018-08-12 | 18.809 | 19.799 | 18.809 01 19.799
10028059 | UN | 04 | 00 | P1 | 2019-08-12 | 2023-09-12 | 19.279 | 20.294 | 19.279 01 20.294
10028059 | UN | 04 | 01 | P1 | 2024-09-12 | 2022-10-12 | 19.761 | 20.801 | 19.761 01 20.801
10028059 | UN | 04 | 00 | P1 | 2023-10-12 | 1999-12-31 | 20.304 | 21.373 | 20.304 04 21.373 SELECT
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
--, C.Pay_Group
, MIN(C.Pay_Level) AS Pay_Level_Min
, MAX(c.Pay_Level) AS Pay_Level_max
, Pay_Scale_Start_Dt
, Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
, Min(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) AS Pay_Rate_Min
, Max(C.Pay_Rate) AS Pay_Rate_Max
FROM 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_Max = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
WHERE a.job_tcd = '10028059'
GROUP BY
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
ORDER BY
Max(C.Pay_Rate)
;
-------------------------------------------------------------------------------
NB, used MSSQL at sqlfiddle for this (DB2 unavailable)
http://sqlfiddle.com/#!3/2908e/4
btw: line 12 and 13 immediately above
if you do need NVL(), you might consider putting it outside the SUM() instead of inside
, NVL( Min(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) ) AS Pay_Rate_Min
, NVL( Max(C.Pay_Rate) ) AS Pay_Rate_MAX
an NVL can "corrupt" or "influence" the results of aggregations
if you do need NVL(), you might consider putting it outside the SUM() instead of inside
, NVL( Min(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) ) AS Pay_Rate_Min
, NVL( Max(C.Pay_Rate) ) AS Pay_Rate_MAX
an NVL can "corrupt" or "influence" the results of aggregations
ASKER
All c5 should be P1and c4 should be 01 02 or 03 as we want MIN(C.Pay_Level) and
MAX(c.Pay_Level) based upon
S_JOB_PAY_SCALE:
job_tcd, pay_scale_area_tcd, pay_scale_type_tcd, pay_group_min, pay_level_min, pay_group_max, pay_level_max
,jps_start_dt,jps_end_dt
10028059 UN 04 0106 P1 0106 03 03-01-01 03-12-31
10028059 UN 04 0106 P1 0106 02 04-01-01 04-12-31
10028059 UN 04 0106 P1 0106 01 05-01-01 09-04-27
10028059 UN 04 0106 P1 0106 01 09-04-28 99-12-31
MAX(c.Pay_Level) based upon
S_JOB_PAY_SCALE:
job_tcd, pay_scale_area_tcd, pay_scale_type_tcd, pay_group_min, pay_level_min, pay_group_max, pay_level_max
,jps_start_dt,jps_end_dt
10028059 UN 04 0106 P1 0106 03 03-01-01 03-12-31
10028059 UN 04 0106 P1 0106 02 04-01-01 04-12-31
10028059 UN 04 0106 P1 0106 01 05-01-01 09-04-27
10028059 UN 04 0106 P1 0106 01 09-04-28 99-12-31
I'm sorry sam2929, I don't see how the detail above helps me limit
c4 to '01','02','03'
c5 to 'P1'
btw: I used the abbreviations C2,C3 etc to help display the result,
they align to each of the select list rows
C4 is MIN(C.Pay_Level)
C5 is MAX(c.Pay_Level)
Are you saying there should be additional conditions in the where clause? like this:
WHERE a.job_tcd = '10028059'
and c.Pay_Level in ('P1','01','02','03')
(I tried this, here is the result)
c4 to '01','02','03'
c5 to 'P1'
btw: I used the abbreviations C2,C3 etc to help display the result,
they align to each of the select list rows
C4 is MIN(C.Pay_Level)
C5 is MAX(c.Pay_Level)
Are you saying there should be additional conditions in the where clause? like this:
WHERE a.job_tcd = '10028059'
and c.Pay_Level in ('P1','01','02','03')
(I tried this, here is the result)
JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
-- -----|----|----|----|----|------------|------------|--------------|--------------|
10028059 | UN | 04 | 01 | P1 | 2001-03-01 | 2031-03-12 | 14.713 | 17.038 |
10028059 | UN | 04 | 01 | P1 | 2001-04-01 | 2023-04-12 | 15.878 | 17.549 |
10028059 | UN | 04 | 01 | P1 | 2024-04-12 | 2022-05-12 | 17.171 | 18.075 |
10028059 | UN | 04 | 02 | 02 | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
10028059 | UN | 04 | 01 | P1 | 2023-05-12 | 2021-06-12 | 17.686 | 18.617 |
10028059 | UN | 04 | 01 | P1 | 2022-06-12 | 2020-07-12 | 18.217 | 19.176 |
10028059 | UN | 04 | 01 | P1 | 2021-07-12 | 2018-08-12 | 18.809 | 19.799 |
10028059 | UN | 04 | 01 | P1 | 2019-08-12 | 2023-09-12 | 19.279 | 20.294 |
10028059 | UN | 04 | 01 | P1 | 2024-09-12 | 2022-10-12 | 19.761 | 20.801 |
10028059 | UN | 04 | 01 | P1 | 2023-10-12 | 1999-12-31 | 20.304 | 21.373 |
I may have missed something in the join conditions along the way
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
Placing this back into the query (and ignoring the post immediately above)
The result is:
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
Placing this back into the query (and ignoring the post immediately above)
The result is:
JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
---------|----|----|----|----|------------|------------|--------------|--------------|
10028059 | UN | 04 | 02 | P1 | 2001-03-01 | 2031-03-12 | 14.713 | 17.038 |
10028059 | UN | 04 | 01 | P1 | 2001-04-01 | 2023-04-12 | 15.878 | 17.549 |
10028059 | UN | 04 | 01 | P1 | 2024-04-12 | 2022-05-12 | 17.171 | 18.075 |
10028059 | UN | 04 | 02 | 02 | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
10028059 | UN | 04 | 01 | P1 | 2023-05-12 | 2021-06-12 | 17.686 | 18.617 |
10028059 | UN | 04 | 01 | P1 | 2022-06-12 | 2020-07-12 | 18.217 | 19.176 |
10028059 | UN | 04 | 01 | P1 | 2021-07-12 | 2018-08-12 | 18.809 | 19.799 |
10028059 | UN | 04 | 01 | P1 | 2019-08-12 | 2023-09-12 | 19.279 | 20.294 |
10028059 | UN | 04 | 01 | P1 | 2024-09-12 | 2022-10-12 | 19.761 | 20.801 |
10028059 | UN | 04 | 01 | P1 | 2023-10-12 | 1999-12-31 | 20.304 | 21.373 | SELECT
A.JOB_TCD
, C.Pay_Scale_Area_Tcd AS c2
, C.Pay_Scale_Type_Tcd AS c3
--, C.Pay_Group
, MIN(C.Pay_Level) AS c4 --Pay_Level_Min
, MAX(c.Pay_Level) AS c5 --Pay_Level_max
, convert(varchar(10),C.Pay_Scale_Start_Dt,121) AS c6
, convert(varchar(10),C.Pay_Scale_End_Dt, 121) AS c7
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
, Min(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) AS Pay_Rate_Min
, Max(C.Pay_Rate) AS Pay_Rate_Max
FROM 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_Max = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
WHERE a.job_tcd = '10028059'
GROUP BY
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
ORDER BY
Max(C.Pay_Rate)**MS SQL Server 2008 Schema Setup**:
CREATE TABLE S_JOB_PAY_SCALE
( [job_tcd] int, [pay_scale_area_tcd] varchar(2), [pay_scale_type_tcd] varchar(2)
, [pay_group_min] varchar(4), [pay_level_min] varchar(2), [pay_group_max] varchar(4)
, [pay_level_max] varchar(4), [jps_start_dt] datetime, [jps_end_dt] datetime)
;
INSERT INTO S_JOB_PAY_SCALE
([job_tcd], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_group_min], [pay_level_min], [pay_group_max], [pay_level_max], [jps_start_dt], [jps_end_dt])
VALUES
(10028059, 'UN', '04', '0106', 'P1', '0106', '03', '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '02', '2001-04-01 00:00:00', '2031-04-12 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '01', '2001-05-01 00:00:00', '2027-09-04 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '01', '2028-09-04 00:00:00', '1999-12-31 00:00:00')
;
CREATE TABLE S_PAY_SCALE
([pay_group] int, [pay_level] varchar(2), [pay_scale_area_tcd] varchar(2), [pay_scale_type_tcd] varchar(2), [pay_rate] money, [pay_scale_start_dt] datetime, [pay_scale_end_dt] datetime)
;
INSERT INTO S_PAY_SCALE
([pay_group], [pay_level], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_rate], [pay_scale_start_dt], [pay_scale_end_dt])
VALUES
(0106, '00', 'UN', '04', 0, '2024-04-12 00:00:00', '2022-05-12 00:00:00'),
(0106, '00', 'UN', '04', 0, '2022-06-12 00:00:00', '2020-07-12 00:00:00'),
(0106, '00', 'UN', '04', 0, '2019-08-12 00:00:00', '2023-09-12 00:00:00'),
(0106, '00', 'UN', '04', 0, '2023-10-12 00:00:00', '1999-12-31 00:00:00'),
(0106, '01', 'UN', '04', 15.487, '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(0106, '01', 'UN', '04', 16.714, '2001-04-01 00:00:00', '2023-04-12 00:00:00'),
(0106, '01', 'UN', '04', 18.075, '2024-04-12 00:00:00', '2022-05-12 00:00:00'),
(0106, '01', 'UN', '04', 18.617, '2023-05-12 00:00:00', '2021-06-12 00:00:00'),
(0106, '01', 'UN', '04', 19.176, '2022-06-12 00:00:00', '2020-07-12 00:00:00'),
(0106, '01', 'UN', '04', 19.799, '2021-07-12 00:00:00', '2018-08-12 00:00:00'),
(0106, '01', 'UN', '04', 20.294, '2019-08-12 00:00:00', '2023-09-12 00:00:00'),
(0106, '01', 'UN', '04', 20.801, '2024-09-12 00:00:00', '2022-10-12 00:00:00'),
(0106, '01', 'UN', '04', 21.373, '2023-10-12 00:00:00', '1999-12-31 00:00:00'),
(0106, '02', 'UN', '04', 16.227, '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(0106, '02', 'UN', '04', 17.549, '2001-04-01 00:00:00', '2023-04-12 00:00:00'),
(0106, '02', 'UN', '04', 18.075, '2024-04-12 00:00:00', '2031-04-12 00:00:00'),
(0106, '03', 'UN', '04', 17.038, '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(0106, 'P1', 'UN', '04', 14.713, '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(0106, 'P1', 'UN', '04', 15.878, '2001-04-01 00:00:00', '2023-04-12 00:00:00'),
(0106, 'P1', 'UN', '04', 17.171, '2024-04-12 00:00:00', '2022-05-12 00:00:00'),
(0106, 'P1', 'UN', '04', 17.686, '2023-05-12 00:00:00', '2021-06-12 00:00:00'),
(0106, 'P1', 'UN', '04', 18.217, '2022-06-12 00:00:00', '2020-07-12 00:00:00'),
(0106, 'P1', 'UN', '04', 18.809, '2021-07-12 00:00:00', '2018-08-12 00:00:00'),
(0106, 'P1', 'UN', '04', 19.279, '2019-08-12 00:00:00', '2023-09-12 00:00:00'),
(0106, 'P1', 'UN', '04', 19.761, '2024-09-12 00:00:00', '2022-10-12 00:00:00'),
(0106, 'P1', 'UN', '04', 20.304, '2023-10-12 00:00:00', '1999-12-31 00:00:00'),
(0106, 'P2', 'UN', '04', 15.416, '2001-03-01 00:00:00', '2031-03-12 00:00:00'),
(0106, 'P2', 'UN', '04', 16.672, '2001-04-01 00:00:00', '2023-04-12 00:00:00'),
(0106, 'P2', 'UN', '04', 17.171, '2024-04-12 00:00:00', '2031-04-12 00:00:00'),
(0106, 'P3', 'UN', '04', 16.186, '2001-03-01 00:00:00', '2031-03-12 00:00:00')
;
http://sqlfiddle.com/#!3/128d0/2ASKER
small problem for this row we don't have P1 so can we do
10028059 | UN | 04 | 02 | '02' | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
can we do as below as P1 don't fall in that range
10028059 | UN | 04 | 02 | ''No data' | 2024-04-12 | 2031-04-12 | 0 | 18.075 |
10028059 | UN | 04 | 02 | '02' | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
can we do as below as P1 don't fall in that range
10028059 | UN | 04 | 02 | ''No data' | 2024-04-12 | 2031-04-12 | 0 | 18.075 |
Replace the existing order by with:
HAVING MAX(c.Pay_Level) = 'P1'
ORDER BY
Max(C.Pay_Rate)
this will remove that unwanted row. Is that what you want?
OR
do you really want that 'No Data' value, and keep that row?
HAVING MAX(c.Pay_Level) = 'P1'
ORDER BY
Max(C.Pay_Rate)
this will remove that unwanted row. Is that what you want?
OR
do you really want that 'No Data' value, and keep that row?
ASKER
there is problem in create sql so i changed them also issue with result after talking to client
they want below result.
10028059 | UN | 04 | 02 | 02 | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
should be
10028059 | UN | 04 | 02 | P1 | 2024-04-12 | 2031-04-12 | 17.71 | 18.075 |
the rate is 17.171. The date range for P1 is 2004-12-24 to 2005-12-22, but number of rows is correct.
they want below result.
10028059 | UN | 04 | 02 | 02 | 2024-04-12 | 2031-04-12 | 18.075 | 18.075 |
should be
10028059 | UN | 04 | 02 | P1 | 2024-04-12 | 2031-04-12 | 17.71 | 18.075 |
the rate is 17.171. The date range for P1 is 2004-12-24 to 2005-12-22, but number of rows is correct.
INSERT INTO S_JOB_PAY_SCALE
([job_tcd], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_group_min], [pay_level_min], [pay_group_max], [pay_level_max], [jps_start_dt], [jps_end_dt])
VALUES
(10028059, 'UN', '04', '0106', 'P1', '0106', '03', '2003-01-01 00:00:00', '2003-12-31 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '02', '2004-01-01 00:00:00', '2004-12-31 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '01', '2005-01-01 00:00:00', '2009-04-27 00:00:00'),
(10028059, 'UN', '04', '0106', 'P1', '0106', '01', '2009-04-28 00:00:00', '9999-12-31 00:00:00')
;
INSERT INTO S_PAY_SCALE
([pay_group], [pay_level], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_rate], [pay_scale_start_dt], [pay_scale_end_dt])
VALUES
(012006,00,UN,04,0,2003-01-01,9999-12-31),
(012006,01,UN,04,15.487,2003-01-01,2003-12-31),
(012006,01,UN,04,16.714,2004-01-01,2004-12-23),
(012006,01,UN,04,18.20075,2004-12-24,2005-12-22),
(012006,01,UN,04,18.617,2005-12-23,2006-12-21),
(012006,01,UN,04,19.176,2006-12-22,2007-12-20),
(012006,01,UN,04,19.799,2007-12-21,2008-12-18),
(012006,01,UN,04,20.294,2008-12-19,2009-12-23),
(012006,01,UN,04,20.801,2009-12-24,2010-12-22),
(012006,01,UN,04,21.373,2010-12-23,9999-12-31),
(012006,02,UN,04,16.227,2003-01-01,2003-12-31),
(012006,02,UN,04,17.549,2004-01-01,2004-12-23),
(012006,02,UN,04,18.075,2004-12-24,2004-12-31),
(012006,2003,UN,04,17.038,2003-01-01,2003-12-31),
(012006,P1,UN,04,14.713,2003-01-01,2003-12-31),
(012006,P1,UN,04,15.878,2004-01-01,2004-12-23),
(012006,P1,UN,04,17.171,2004-12-24,2005-12-22),
(012006,P1,UN,04,17.686,2005-12-23,2006-12-21),
(012006,P1,UN,04,18.217,2006-12-22,2007-12-20),
(012006,P1,UN,04,18.809,2007-12-21,2008-12-18),
(012006,P1,UN,04,19.279,2008-12-19,2009-12-23),
(012006,P1,UN,04,19.761,2009-12-24,2010-12-22),
(012006,P1,UN,04,20.304,2010-12-23,9999-12-31),
(012006,P2,UN,04,15.416,2003-01-01,2003-12-31)
;
I had to fiddle with that latest data as
(10028059, 'UN', '04', '0106'
does not match to:
(012006,00,UN,
please review the information below,
it may pay you to visit http://sqlfiddle.com/#!3/ba211/1 directly
The sql query has not changed. If this result isn't meeting expectation could you explain what the missing logic is?
10028059 | UN | 04 | 02 | P1 | 2024-04-12 | 2031-04-12 | 17.71 | 18.075 |
(10028059, 'UN', '04', '0106'
does not match to:
(012006,00,UN,
please review the information below,
it may pay you to visit http://sqlfiddle.com/#!3/ba211/1 directly
The sql query has not changed. If this result isn't meeting expectation could you explain what the missing logic is?
**MS SQL Server 2008 Schema Setup**:
Note that although I'm using sql server for this I could have used Oracle which has more function name similarities. Thea reason I didn't is that theSQLfiddles 'text to ddl' it slightly better in mssql that oracle in my experience.
CREATE TABLE S_JOB_PAY_SCALE
( [job_tcd] varchar(8), [pay_scale_area_tcd] varchar(4), [pay_scale_type_tcd] varchar(2)
, [pay_group_min] varchar(6), [pay_level_min] varchar(6), [pay_group_max] varchar(6)
, [pay_level_max] varchar(6), [jps_start_dt] datetime, [jps_end_dt] datetime)
;
INSERT INTO S_JOB_PAY_SCALE
([job_tcd], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_group_min], [pay_level_min], [pay_group_max], [pay_level_max], [jps_start_dt], [jps_end_dt])
VALUES
('10028059', 'UN', '04', '012006', 'P1', '012006', '03', '2003-01-01 00:00:00', '2003-12-31 00:00:00'),
('10028059', 'UN', '04', '012006', 'P1', '012006', '02', '2004-01-01 00:00:00', '2004-12-31 00:00:00'),
('10028059', 'UN', '04', '012006', 'P1', '012006', '01', '2005-01-01 00:00:00', '2009-04-27 00:00:00'),
('10028059', 'UN', '04', '012006', 'P1', '012006', '01', '2009-04-28 00:00:00', '9999-12-31 00:00:00')
;
CREATE TABLE S_PAY_SCALE
([pay_group] varchar(6), [pay_level] varchar(4), [pay_scale_area_tcd] varchar(2), [pay_scale_type_tcd] varchar(2), [pay_rate] money, [pay_scale_start_dt] datetime, [pay_scale_end_dt] datetime)
;
INSERT INTO S_PAY_SCALE
([pay_group], [pay_level], [pay_scale_area_tcd], [pay_scale_type_tcd], [pay_rate], [pay_scale_start_dt], [pay_scale_end_dt])
VALUES
('012006','00','UN','04',0,'2003-01-01','2099-12-31'),
('012006','01','UN','04',15.487,'2003-01-01','2003-12-31'),
('012006','01','UN','04',16.714,'2004-01-01','2004-12-23'),
('012006','01','UN','04',18.20075,'2004-12-24','2005-12-22'),
('012006','01','UN','04',18.617,'2005-12-23','2006-12-21'),
('012006','01','UN','04',19.176,'2006-12-22','2007-12-20'),
('012006','01','UN','04',19.799,'2007-12-21','2008-12-18'),
('012006','01','UN','04',20.294,'2008-12-19','2009-12-23'),
('012006','01','UN','04',20.801,'2009-12-24','2010-12-22'),
('012006','01','UN','04',21.373,'2010-12-23','9999-12-31'),
('012006','02','UN','04',16.227,'2003-01-01','2003-12-31'),
('012006','02','UN','04',17.549,'2004-01-01','2004-12-23'),
('012006','02','UN','04',18.075,'2004-12-24','2004-12-31'),
('012006','2003','UN','04',17.038,'2003-01-01','2003-12-31'),
('012006','P1','UN','04',14.713,'2003-01-01','2003-12-31'),
('012006','P1','UN','04',15.878,'2004-01-01','2004-12-23'),
('012006','P1','UN','04',17.171,'2004-12-24','2005-12-22'),
('012006','P1','UN','04',17.686,'2005-12-23','2006-12-21'),
('012006','P1','UN','04',18.217,'2006-12-22','2007-12-20'),
('012006','P1','UN','04',18.809,'2007-12-21','2008-12-18'),
('012006','P1','UN','04',19.279,'2008-12-19','2009-12-23'),
('012006','P1','UN','04',19.761,'2009-12-24','2010-12-22'),
('012006','P1','UN','04',20.304,'2010-12-23','9999-12-31'),
('012006','P2','UN','04',15.416,'2003-01-01','2003-12-31')
;
**Query 1**:
SELECT
A.JOB_TCD
, C.Pay_Scale_Area_Tcd AS c2
, C.Pay_Scale_Type_Tcd AS c3
--, C.Pay_Group
, MIN(C.Pay_Level) AS c4 --Pay_Level_Min
, MAX(c.Pay_Level) AS c5 --Pay_Level_max
, convert(varchar(10),C.Pay_Scale_Start_Dt,121) AS c6
, convert(varchar(10),C.Pay_Scale_End_Dt, 121) AS c7
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
, Min(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) AS Pay_Rate_Min
, Max(C.Pay_Rate) AS Pay_Rate_Max
FROM 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_Max = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
WHERE a.job_tcd = '10028059'
GROUP BY
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
ORDER BY
Max(C.Pay_Rate)
10028059 | UN | 04 | 02 | P1 | 2024-04-12 | 2031-04-12 | 17.71 | 18.075 |
**[Results][2]**:
| JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----|----|------------|------------|--------------|--------------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
[1]: http://sqlfiddle.com/#!3/ba211/1
[2]: http://sqlfiddle.com/#!3/ba211/1/0ASKER
This solution is for oracle :
Below record has issue:
10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 |
as column C5 gives MAX(c.Pay_Level) which is P1 as P1 falls between range 2004-12-24 to 2004-12-31 and its rate is 17.171 for that range so record should like below as pay rate of P1 during ,'2004-12-24' to '2005-12-22' is 17.171
10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
so two changes C5 should be P1 and rate should be 17.171
Below S_PAY_SCALE P1 records fall ,'2004-12-24' to '2005-12-22'
S_PAY_SCALE:
'012006','P1','UN','04',17
Below record has issue:
10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 |
as column C5 gives MAX(c.Pay_Level) which is P1 as P1 falls between range 2004-12-24 to 2004-12-31 and its rate is 17.171 for that range so record should like below as pay rate of P1 during ,'2004-12-24' to '2005-12-22' is 17.171
10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
so two changes C5 should be P1 and rate should be 17.171
Below S_PAY_SCALE P1 records fall ,'2004-12-24' to '2005-12-22'
S_PAY_SCALE:
'012006','P1','UN','04',17
I'm sorry, you may make the data changes at sqlfiddle, or provide scripts that will work without extensive edits - please do recognize I have done quite a deal so far - and I regret that I am not going to try and reconcile the sample data to notes held here. You are in a much better position to know that data and recognize any flaws in it.
Note too that the data types of fields are 'assumed' from that data supplied so far. If you have create table script and inserts for Oracle you can use Oracle 11g at sqlfiddle. This way we will both know the data and tables are accurate.
I will change the Topic to Oracle. I was wondering how you were using NVL etc.
Note too that the data types of fields are 'assumed' from that data supplied so far. If you have create table script and inserts for Oracle you can use Oracle 11g at sqlfiddle. This way we will both know the data and tables are accurate.
I will change the Topic to Oracle. I was wondering how you were using NVL etc.
ASKER
i used http://sqlfiddle.com/#!3/ba211/1
the issue remains same as discussed above do we need lag function to check the previous date?
10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 |
should be
10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
as column C5 gives MAX(c.Pay_Level) which is P1 as P1 falls between range 2004-12-24 to 2004-12-31 and its rate is 17.171 for that range so record should like below as pay rate of P1 during ,'2004-12-24' to '2005-12-22' is 17.171
the issue remains same as discussed above do we need lag function to check the previous date?
10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 |
should be
10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
as column C5 gives MAX(c.Pay_Level) which is P1 as P1 falls between range 2004-12-24 to 2004-12-31 and its rate is 17.171 for that range so record should like below as pay rate of P1 during ,'2004-12-24' to '2005-12-22' is 17.171
Please note the following, it REMOVES the "problem" row (and hence there are 9 rows)
In the original expected results there were 9 rows
Also note I have moved this to Oracle to remove all possibility of dbms differences
In the original expected results there were 9 rows
Also note I have moved this to Oracle to remove all possibility of dbms differences
| JOB_TCD | PAY_SCALE_AREA_TCD | PAY_SCALE_TYPE_TCD | PAY_LEVEL_MIN | PAY_LEVEL_MAX | PAY_SCALE_START_DT | PAY_SCALE_END_DT | PAY_RATE_MIN | PAY_RATE_MAX |
------------|-----------------------|-----------------------|------------------|------------------|-----------------------|---------------------|-----------------|-----------------|--
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |**Oracle 11g R2 Schema Setup**:
CREATE TABLE S_JOB_PAY_SCALE
( JOB_TCD varchar2(12)
, PAY_SCALE_AREA_TCD varchar2(12)
, PAY_SCALE_TYPE_TCD varchar2(12)
, PAY_GROUP_MIN varchar2(12)
, PAY_LEVEL_MIN varchar2(12)
, PAY_GROUP_MAX varchar2(12)
, PAY_LEVEL_MAX varchar2(12)
, JPS_START_DT timestamp
, JPS_END_DT timestamp
)
;
INSERT ALL
INTO S_JOB_PAY_SCALE (JOB_TCD, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP_MIN, PAY_LEVEL_MIN, PAY_GROUP_MAX, PAY_LEVEL_MAX, JPS_START_DT, JPS_END_DT)
VALUES ('10028059', 'UN', '04', '012006', 'P1', '012006', '03', '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
INTO S_JOB_PAY_SCALE (JOB_TCD, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP_MIN, PAY_LEVEL_MIN, PAY_GROUP_MAX, PAY_LEVEL_MAX, JPS_START_DT, JPS_END_DT)
VALUES ('10028059', 'UN', '04', '012006', 'P1', '012006', '02', '01-Jan-2004 12:00:00 AM', '31-Dec-2004 12:00:00 AM')
INTO S_JOB_PAY_SCALE (JOB_TCD, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP_MIN, PAY_LEVEL_MIN, PAY_GROUP_MAX, PAY_LEVEL_MAX, JPS_START_DT, JPS_END_DT)
VALUES ('10028059', 'UN', '04', '012006', 'P1', '012006', '01', '01-Jan-2005 12:00:00 AM', '27-Apr-2009 12:00:00 AM')
INTO S_JOB_PAY_SCALE (JOB_TCD, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_GROUP_MIN, PAY_LEVEL_MIN, PAY_GROUP_MAX, PAY_LEVEL_MAX, JPS_START_DT, JPS_END_DT)
VALUES ('10028059', 'UN', '04', '012006', 'P1', '012006', '01', '28-Apr-2009 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
SELECT * FROM dual
;
CREATE TABLE S_PAY_SCALE
(PAY_GROUP varchar2(12)
, PAY_LEVEL varchar2(12)
, PAY_SCALE_AREA_TCD varchar2(12)
, PAY_SCALE_TYPE_TCD varchar2(12)
, PAY_RATE decimal(18,5)
, PAY_SCALE_START_DT timestamp
, PAY_SCALE_END_DT timestamp
)
;
INSERT ALL
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '00', 'UN', '04', 0, '01-Jan-2003 12:00:00 AM', '31-Dec-2099 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 15.487, '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 16.714, '01-Jan-2004 12:00:00 AM', '23-Dec-2004 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 18.20075, '24-Dec-2004 12:00:00 AM', '22-Dec-2005 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 18.617, '23-Dec-2005 12:00:00 AM', '21-Dec-2006 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 19.176, '22-Dec-2006 12:00:00 AM', '20-Dec-2007 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 19.799, '21-Dec-2007 12:00:00 AM', '18-Dec-2008 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 20.294, '19-Dec-2008 12:00:00 AM', '23-Dec-2009 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 20.801, '24-Dec-2009 12:00:00 AM', '22-Dec-2010 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '01', 'UN', '04', 21.373, '23-Dec-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '02', 'UN', '04', 16.227, '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '02', 'UN', '04', 17.549, '01-Jan-2004 12:00:00 AM', '23-Dec-2004 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '02', 'UN', '04', 18.075, '24-Dec-2004 12:00:00 AM', '31-Dec-2004 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', '2003', 'UN', '04', 17.038, '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 14.713, '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 15.878, '01-Jan-2004 12:00:00 AM', '23-Dec-2004 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 17.171, '24-Dec-2004 12:00:00 AM', '22-Dec-2005 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 17.686, '23-Dec-2005 12:00:00 AM', '21-Dec-2006 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 18.217, '22-Dec-2006 12:00:00 AM', '20-Dec-2007 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 18.809, '21-Dec-2007 12:00:00 AM', '18-Dec-2008 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 19.279, '19-Dec-2008 12:00:00 AM', '23-Dec-2009 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 19.761, '24-Dec-2009 12:00:00 AM', '22-Dec-2010 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P1', 'UN', '04', 20.304, '23-Dec-2010 12:00:00 AM', '31-Dec-9999 12:00:00 AM')
INTO S_PAY_SCALE (PAY_GROUP, PAY_LEVEL, PAY_SCALE_AREA_TCD, PAY_SCALE_TYPE_TCD, PAY_RATE, PAY_SCALE_START_DT, PAY_SCALE_END_DT)
VALUES ('012006', 'P2', 'UN', '04', 15.416, '01-Jan-2003 12:00:00 AM', '31-Dec-2003 12:00:00 AM')
SELECT * FROM dual
;**Query 1**:
SELECT
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
--, C.Pay_Group
, MIN(C.Pay_Level) AS Pay_Level_Min
, MAX(c.Pay_Level) AS Pay_Level_max
, to_char(C.Pay_Scale_Start_Dt,'YYYY-MM-DD') AS Pay_Scale_Start_Dt
, to_char(C.Pay_Scale_End_Dt,'YYYY-MM-DD') AS Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
, MIN(CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END) AS Pay_Rate_Min
, MAX(C.Pay_Rate) AS Pay_Rate_Max
FROM 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_Max = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
WHERE a.job_tcd = '10028059'
GROUP BY
A.JOB_TCD
, C.Pay_Scale_Area_Tcd
, C.Pay_Scale_Type_Tcd
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
having MAX(c.Pay_Level) = 'P1'
ORDER BY
MAX(C.Pay_Rate)
[1]: http://sqlfiddle.com/#!4/385155/2
[2]: http://sqlfiddle.com/#!4/385155/2/0ASKER
We can't do filter by P1 as this is just sample records
The number of records should be 10 and should look like below
| JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 |P1 | 2004-12-24 | 2004-12-31 | 17.171 | 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
The number of records should be 10 and should look like below
| JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 |P1 | 2004-12-24 | 2004-12-31 | 17.171 | 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
Then I would ask you to explain, in point form, the logic - because I cannot make the figures match that expectation - there is something missing and I don't know what it is.
e.g. Is it a case expression perhaps? but what are the conditions?
There is the sqlfiddle to play with as well.
I can make no promises that with a detailed logic I will 'see' the solution either I'm afraid. Should you want other eyes on this, you may use the 'Request Attention' feature under the question text.
e.g. Is it a case expression perhaps? but what are the conditions?
There is the sqlfiddle to play with as well.
I can make no promises that with a detailed logic I will 'see' the solution either I'm afraid. Should you want other eyes on this, you may use the 'Request Attention' feature under the question text.
ASKER
once below query returnd 10 rows
JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 | line 3
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
How can i do this compare then 10 rows again to table S_PAY_SCALE see if c6 and c7 date range falls between S_PAY_SCALE.
In above case line 3 falls between line 16 so change C6 line 3 above results to P1 and also change line 3 pay_rate to 17.171
S_PAY_SCALE
('012006','00','UN','04',0
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',2
('012006','01','UN','04',2
('012006','01','UN','04',2
('012006','02','UN','04',1
('012006','02','UN','04',1
('012006','02','UN','04',1
('012006','2003','UN','04'
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',2
('012006','P2','UN','04',1
JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | 02 | 2004-12-24 | 2004-12-31 | 18.075 | 18.075 | line 3
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.2008 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
How can i do this compare then 10 rows again to table S_PAY_SCALE see if c6 and c7 date range falls between S_PAY_SCALE.
In above case line 3 falls between line 16 so change C6 line 3 above results to P1 and also change line 3 pay_rate to 17.171
S_PAY_SCALE
('012006','00','UN','04',0
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',1
('012006','01','UN','04',2
('012006','01','UN','04',2
('012006','01','UN','04',2
('012006','02','UN','04',1
('012006','02','UN','04',1
('012006','02','UN','04',1
('012006','2003','UN','04'
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',1
('012006','P1','UN','04',2
('012006','P2','UN','04',1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sam2929
Are you making any progress with this?
Do note it is because the value in PAY_SCALE_END_DT (2004-12-31) is unique to one record and that is why the query is unable to match your expectation (due to group by clause).
I'd like to assist further, but at this point cannot see how.
Are you making any progress with this?
Do note it is because the value in PAY_SCALE_END_DT (2004-12-31) is unique to one record and that is why the query is unable to match your expectation (due to group by clause).
I'd like to assist further, but at this point cannot see how.
ASKER
Hi,
I got this working one small issue still there,
I have overlapping PAY_SCALE_START_DT as dec-24-2004 for PAY_LEVEL_MIN 01 AND 02
The effective date for the first record with a pay level min of 01 should read 01-JAN-05 since this is the first effective date of that pay level range for this job in S_JOB_PAY_SCALE. Otherwise there will be overlapping records.
query:
I got this working one small issue still there,
I have overlapping PAY_SCALE_START_DT as dec-24-2004 for PAY_LEVEL_MIN 01 AND 02
The effective date for the first record with a pay level min of 01 should read 01-JAN-05 since this is the first effective date of that pay level range for this job in S_JOB_PAY_SCALE. Otherwise there will be overlapping records.
query:
SELECT distinct
A.JOB_TCD,
trim(A.Pay_Scale_Area_Tcd),
trim(A.Pay_Scale_Type_Tcd),
trim(A.Pay_Group_min),
MIN(C.Pay_Level) As Pay_Level_Min,
trim(A.Pay_Group_max),
case when MIN(C.Pay_Level)=MAX(c.Pay_Level) then Lead(MAX(c.Pay_Level),1) OVER (ORDER BY A.JOB_TCD) else
MAX(c.Pay_Level) end Pay_Level_max,
--trim(C.Pay_Scale_Area_Desc),
--trim(C.Pay_Scale_Type_Desc),
---Min(Nvl(C.Pay_Rate,0)) As Pay_Rate_Min_test,
case when MIN(C.Pay_Level)=MAX(c.Pay_Level) then Lead(MIN(C.Pay_Rate),1) OVER (ORDER BY A.JOB_TCD) else
MIN(C.Pay_rate) end Pay_Rate_Min,
Max(C.Pay_Rate) As Pay_Rate_Max
--,Min(Nvl(C.Pay_Wage_Type,'-1')) As Pay_Wage_Min
--,Max(NVL(C.Pay_Wage_Type,'-1')) As Pay_Wage_Max
,C.PAY_SCALE_START_DT
,C.PAY_SCALE_END_DT
FROM S_Job_Pay_Scale A
left outer 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 trim(A.Pay_Group_Max) = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt AND A.Jps_End_Dt
)
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
----WHERE a.job_tcd in('10028059')
----where A.job_tcd ='10005172'
Group By
A.JOB_TCD,
A.Pay_Scale_Area_Tcd,
A.Pay_Scale_Type_Tcd,
A.Pay_Group_min,
A.Pay_Group_max,
---C.Pay_Scale_Area_Desc,
--C.Pay_Scale_Type_Desc,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt
ORDER BY
A.JOB_TCD,
C.Pay_Scale_Start_Dt,
C.Pay_Scale_End_Dt,
Max(C.Pay_Rate)
Below is a comparison to my latest, to your latest (reduced columns, and abbreviated headings for simplicity of comparison). There appear to be 3 differences, only you can decide if they are right or wrong but the minimum in row 1 appears wrong to me - on all 'expected results' that has been 14.713
By the way:
a. I placed your latest query into a code block for legibility
b. more importantly: 'distinct' is fully redundant if you are using 'group by'
(by definition the rows produced from 'group by' are unique, so distinct achieves nothing)
* = difference
My Latest output:
| JOB_TCD | APS_AREA | APS_TYPE | PL_MIN | PL_MAX | START_DT | END_DT | PR_MIN | PR_MAX |
-----------|----------|----------|--------|--------|------------|------------|--------|----------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713*| 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | *02 | 2004-12-24 | 2004-12-31 | 18.075*| 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
Your Latest output:
| JOB_TCD | APS_AREA | APS_TYPE | PL_MIN | PL_MAX | START_DT | END_DT | PR_MIN | PR_MAX |
-----------|----------|----------|--------|--------|------------|------------|--------|----------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 15.878*| 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | *P1 | 2004-12-24 | 2004-12-31 | 17.171*| 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |By the way:
a. I placed your latest query into a code block for legibility
b. more importantly: 'distinct' is fully redundant if you are using 'group by'
(by definition the rows produced from 'group by' are unique, so distinct achieves nothing)
Here is a suggestion, instead of basing those case expressions on pay_rate comparison, base them on start date, here is the result:
| JOB_TCD | APS_AREA | APS_TYPE | PL_MIN | PL_MAX | START_DT | END_DT | PR_MIN | PR_MAX |
-----------|----------|----------|--------|--------|------------|------------|--------|----------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713*| 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | *P1 | 2004-12-24 | 2004-12-31 | 17.171*| 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24 | 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |-- using lead on min(start_dt)
SELECT
A.JOB_TCD
, trim(A.Pay_Scale_Area_Tcd) AS APS_area
, trim(A.Pay_Scale_Type_Tcd) AS APS_type
, MIN(C.Pay_Level) AS PL_Min
-- change
, CASE
WHEN MIN(C.PAY_SCALE_START_DT) = LEAD(MIN(C.PAY_SCALE_START_DT),1)
OVER (ORDER BY C.PAY_SCALE_START_DT DESC)
THEN Lead(MAX(c.Pay_Level), 1) OVER (
ORDER BY A.JOB_TCD
)
ELSE MAX(c.Pay_Level)
END AS PL_max
--, trim(C.Pay_Scale_Area_Desc)
--, trim(C.Pay_Scale_Type_Desc)
--, Min(Nvl(C.Pay_Rate,0)) As Pay_Rate_Min_test
--, trim(A.Pay_Group_min) AS APG_min
--, trim(A.Pay_Group_max) AS APG_max
, to_char(C.PAY_SCALE_START_DT,'YYYY-MM-DD') AS Start_DT
, to_char(C.PAY_SCALE_END_DT,'YYYY-MM-DD') AS End_DT
-- change
, CASE
WHEN MIN(C.PAY_SCALE_START_DT) = LEAD(MIN(C.PAY_SCALE_START_DT),1)
OVER (ORDER BY C.PAY_SCALE_START_DT DESC)
THEN Lead(MIN(C.Pay_Rate), 1) OVER (ORDER BY A.JOB_TCD)
ELSE MIN(C.Pay_rate)
END AS PR_min
, Max(C.Pay_Rate) AS PR_Max
-- ,Min(Nvl(C.Pay_Wage_Type,'-1')) As Pay_Wage_Min
-- ,Max(NVL(C.Pay_Wage_Type,'-1')) As Pay_Wage_Max
FROM S_Job_Pay_Scale A
LEFT 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 trim(A.Pay_Group_Max) = C.Pay_Group
AND (
C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt
AND A.Jps_End_Dt
OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt
AND A.Jps_End_Dt
)
AND (
A.Pay_Level_Max = C.Pay_Level
OR A.Pay_Level_Min = C.Pay_Level
)
----WHERE a.job_tcd in('10028059')
----where A.job_tcd ='10005172'
GROUP BY
A.JOB_TCD
, A.Pay_Scale_Area_Tcd
, A.Pay_Scale_Type_Tcd
, A.Pay_Group_min
, A.Pay_Group_max
--, C.Pay_Scale_Area_Desc
--, C.Pay_Scale_Type_Desc
, C.Pay_Scale_Start_Dt
, C.Pay_Scale_End_Dt
ORDER BY
A.JOB_TCD
, Start_DT
, End_DT
, PR_Max
;ASKER
As there is Start_dt overlap so i want date as below
| 10028059 | UN | 04 | 02 | *P1 | 2004-12-24 | 2004-12-31 | 17.171*| 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-01-01 | 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 02 | *P1 | 2004-12-24 | 2004-12-31 | 17.171*| 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-01-01 | 2005-12-22 | 17.171 | 18.20075 |
-- change
, CASE
WHEN MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_DT),1)
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_DT),1)
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_DT,'YYYY-MM-DD')
END AS Start_DT
| JOB_TCD | APS_AREA | APS_TYPE | PL_MIN | PL_MAX | START_DT | END_DT | PR_MIN | PR_MAX |
-----------|----------|----------|--------|--------|------------|------------|--------|----------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | P1 | 2004-12-24 | 2004-12-31 | 17.171 | 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-01-01*| 2005-12-22 | 17.171 | 18.20075 |
| 10028059 | UN | 04 | 01 | P1 | 2005-12-23 | 2006-12-21 | 17.686 | 18.617 |
| 10028059 | UN | 04 | 01 | P1 | 2006-12-22 | 2007-12-20 | 18.217 | 19.176 |
| 10028059 | UN | 04 | 01 | P1 | 2007-12-21 | 2008-12-18 | 18.809 | 19.799 |
| 10028059 | UN | 04 | 01 | P1 | 2008-12-19 | 2009-12-23 | 19.279 | 20.294 |
| 10028059 | UN | 04 | 01 | P1 | 2009-12-24 | 2010-12-22 | 19.761 | 20.801 |
| 10028059 | UN | 04 | 01 | P1 | 2010-12-23 | 9999-12-31 | 20.304 | 21.373 |
if you compare the "should look like this" table provided at http:#a39505396, you will see that at that time you did not say:
"the periods should not overlap"
and the table provided at that point indicates periods are permitted to overlap
| JOB_TCD | C2 | C3 | C4 | C5 | C6 | C7 | PAY_RATE_MIN | PAY_RATE_MAX |
|----------|----|----|----|----|------------|------------|--------------|--------------|
| 10028059 | UN | 04 | P1 | P1 | 2003-01-01 | 2003-12-31 | 14.713 | 14.713 |
| 10028059 | UN | 04 | 02 | P1 | 2004-01-01 | 2004-12-23 | 15.878 | 17.549 |
| 10028059 | UN | 04 | 02 | P1 | 2004-12-24 | 2004-12-31*| 17.171 | 18.075 |
| 10028059 | UN | 04 | 01 | P1 | 2004-12-24*| 2005-12-22 | 17.171 | 18.2008 |ASKER
getting below error after adding
CASE
WHEN MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
CASE
WHEN MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
against what data?
it's related to the data I think
I assume C.PAY_SCALE_START_DT is actually a date field
perhaps remove:
to_char( ,'YYYY-MM-DD')
(used twice)
and re-try
nb: the to_char() I introduced for output consistency - you might not need them in the query at all - but that is up to you.
if you run into trouble, look at http:#a39506216 , you may need a simpler select statement like that to inspect the data, but also add the lead() columns now used in the case expressions.
Please note I'll do what I can if you hit errors such as this - but in most cases they have to be ironed out by you - as only you have access to the real tables/data.
it's related to the data I think
I assume C.PAY_SCALE_START_DT is actually a date field
perhaps remove:
to_char( ,'YYYY-MM-DD')
(used twice)
and re-try
nb: the to_char() I introduced for output consistency - you might not need them in the query at all - but that is up to you.
if you run into trouble, look at http:#a39506216 , you may need a simpler select statement like that to inspect the data, but also add the lead() columns now used in the case expressions.
Please note I'll do what I can if you hit errors such as this - but in most cases they have to be ironed out by you - as only you have access to the real tables/data.
ASKER
when i do
where to_char(C.PAY_SCALE_END_DT
can in case stmt we can ignore '9999-12-31'+1 records
CASE
WHEN MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
where to_char(C.PAY_SCALE_END_DT
can in case stmt we can ignore '9999-12-31'+1 records
CASE
WHEN MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
CASE
WHEN MIN(C.PAY_SCALE_START_DT) < '99991231' AND
MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
I'd just exclude from consideration and if this situation ever arose there's nothing you can do about it (i.e. you can't add 1)
WHEN MIN(C.PAY_SCALE_START_DT) < '99991231' AND
MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
I'd just exclude from consideration and if this situation ever arose there's nothing you can do about it (i.e. you can't add 1)
Mmm, it might have to be:
CASE
WHEN LEAD(MAX(C.PAY_SCALE_END_D
MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
CASE
WHEN LEAD(MAX(C.PAY_SCALE_END_D
MIN(C.PAY_SCALE_START_DT) < LEAD(MAX(C.PAY_SCALE_END_D
OVER (ORDER BY C.PAY_SCALE_END_DT DESC)
THEN to_char( (LEAD(MAX(C.PAY_SCALE_END_
OVER (ORDER BY C.PAY_SCALE_END_DT DESC) ) + 1 ,'YYYY-MM-DD')
ELSE to_char(C.PAY_SCALE_START_
END AS Start_DT
How is this going? Are we there yet?
Hi, thanks for the points - assume you have this solved now. Cheers, Paul
> distinct is 100% redundant in a query that is using group by
> you cannot use aliases established in the select clause inside the where clause*
> applying functions to data in the where clause adversely affects performance
(it is "non-sargable")
I have noted these in the code:
Open in new window
* select list aliases:I believe this restriction exists in DB2, it certainly does in SQL Server and Oracle amongst others - the only exception I know of is TerraData
{+edit, missed a comment and some typos fixed.}
{edit 2 - very sorry}
> be careful if using 'between' for date ranges,
here however it is used as a join condition which appears to be appropriate.