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

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_scale_area_tcd,pay_scale_type_tcd,pay_rate,pay_scale_start_dt,pay_scale_end_dt
from 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
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,'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 (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
0
sam2929
Asked:
sam2929
  • 23
  • 12
1 Solution
 
PortletPaulfreelancerCommented:
There are a number of syntax issues with your current query:
> 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:
SELECT /* DISTINCT */ /* distinct is fully redundant in a group by query */
       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) -- cannot use select list aliases here 
     AND (
          trim(A.Pay_Level_Max) = trim(C.Pay_Level) -- cannot use select list aliases here & non-sargable
          OR trim(A.Pay_Level_Min) = trim(C.Pay_Level) -- cannot use select list aliases here & non-sargable
          )
     AND (
          C.Pay_Scale_Start_Dt BETWEEN A.Jps_Start_Dt -- Beware of Between
               AND A.Jps_End_Dt
          OR C.Pay_Scale_End_Dt BETWEEN A.Jps_Start_Dt -- Beware of Between
               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

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.
0
 
PortletPaulfreelancerCommented:
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)
          )
;

Open in new window

0
 
sam2929Author Commented:
Adding having give below error
not a group by expression
0
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.

 
PortletPaulfreelancerCommented:
ah, yes of course sorry, unable to get to this until tomorrow.
0
 
PortletPaulfreelancerCommented:
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:
  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 

Open in new window

**Query**:
    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

Open in new window

0
 
PortletPaulfreelancerCommented:
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
0
 
sam2929Author Commented:
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
0
 
PortletPaulfreelancerCommented:
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)
 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 |

Open in new window

0
 
PortletPaulfreelancerCommented:
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:
 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 |

Open in new window

The query for this is:
    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)

Open in new window

and the data it is based on:
**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/2

Open in new window

0
 
sam2929Author Commented:
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 |
0
 
PortletPaulfreelancerCommented:
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?
0
 
sam2929Author Commented:
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.


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)
;

Open in new window

0
 
PortletPaulfreelancerCommented:
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?
**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)
    

Open in new window

latest provided expected:
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/0

Open in new window

0
 
sam2929Author Commented:
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.171,'2004-12-24','2005-12-22'),
0
 
PortletPaulfreelancerCommented:
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.
0
 
sam2929Author Commented:
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
0
 
PortletPaulfreelancerCommented:
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
|  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 |

Open in new window

**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
    ;

Open in new window

**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/0

Open in new window

0
 
sam2929Author Commented:
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 |
0
 
PortletPaulfreelancerCommented:
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.
0
 
sam2929Author Commented:
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,'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'),  ---line 16
    ('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')
0
 
PortletPaulfreelancerCommented:
Line 3 is produced as a separate line (based in line 16 in the able)in the summary because it is grouped by dates

    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


and the combination of | 2004-12-24 | 2004-12-31 | is unique to table line 16

Here is a comparison of details to the summary lines:
|-------------------------------------- DETAILS  -----------------------------------------|
| PAY_LEVEL | PAY_SCALE_START_DT | PAY_SCALE_END_DT | PAY_RATE_FOR_MIN | PAY_RATE_FOR_MAX | Summary
------------|--------------------|------------------|------------------|------------------|--------
|        P1 |         2003-01-01 |       2003-12-31 |           14.713 |           14.713 | line 1

|        P1 |         2004-01-01 |       2004-12-23 |           15.878 |           15.878 | line 2
|        02 |         2004-01-01 |       2004-12-23 |           17.549 |           17.549 | line 2

#        02 #         2004-12-24 #       2004-12-31 #           18.075 #           18.075 # line 3

|        01 |         2004-12-24 |       2005-12-22 |         18.20075 |         18.20075 | line 4
|        P1 |         2004-12-24 |       2005-12-22 |           17.171 |           17.171 | line 4
|        P1 |         2004-12-24 |       2005-12-22 |           17.171 |           17.171 | line 4

|        01 |         2005-12-23 |       2006-12-21 |           18.617 |           18.617 | line 5
|        P1 |         2005-12-23 |       2006-12-21 |           17.686 |           17.686 | line 5

|        01 |         2006-12-22 |       2007-12-20 |           19.176 |           19.176 | line 6
|        P1 |         2006-12-22 |       2007-12-20 |           18.217 |           18.217 | line 6

|        01 |         2007-12-21 |       2008-12-18 |           19.799 |           19.799 | line 7
|        P1 |         2007-12-21 |       2008-12-18 |           18.809 |           18.809 | line 7

|        01 |         2008-12-19 |       2009-12-23 |           20.294 |           20.294 | line 8
|        01 |         2008-12-19 |       2009-12-23 |           20.294 |           20.294 | line 8
|        P1 |         2008-12-19 |       2009-12-23 |           19.279 |           19.279 | line 8
|        P1 |         2008-12-19 |       2009-12-23 |           19.279 |           19.279 | line 8

|        P1 |         2009-12-24 |       2010-12-22 |           19.761 |           19.761 | line 9
|        01 |         2009-12-24 |       2010-12-22 |           20.801 |           20.801 | line 9

|        P1 |         2010-12-23 |       9999-12-31 |           20.304 |           20.304 | line 10
|        01 |         2010-12-23 |       9999-12-31 |           21.373 |           21.373 | line 10

Open in new window

produced by this query
SELECT
       C.Pay_Level 
     , 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
     , CASE WHEN C.Pay_Rate > 0 THEN C.Pay_Rate END AS Pay_Rate_for_Min
     , C.Pay_Rate                                   AS Pay_Rate_For_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'
ORDER BY
       A.JOB_TCD
     , C.Pay_Scale_Area_Tcd
     , C.Pay_Scale_Type_Tcd
     , C.Pay_Scale_Start_Dt
     , C.Pay_Scale_End_Dt
;

Open in new window

Your task is to describe (in words) why that unique combination of dates should be ignored and somehow produce min/max numbers that don't appear to exist.

i.e. you need to arrive at a logic for your expected result on that line
& that logic must not disturb the existing lines (or produce the same results)

Over to you.
I will not be working on this for at least 12 hours, and I cannot read your mind through this browser - I only get to see the text.
(sorry no offence intended, but I really don't follow your comments on line 3 so far).
0
 
PortletPaulfreelancerCommented:
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.
0
 
sam2929Author Commented:
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:
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)

Open in new window

http://sqlfiddle.com/#!4/385155/15
0
 
PortletPaulfreelancerCommented:
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

                      * = 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 |

Open in new window

http://sqlfiddle.com/#!4/385155/35

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)
0
 
PortletPaulfreelancerCommented:
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 |

Open in new window

and the query is:
-- 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
;

Open in new window

http://sqlfiddle.com/#!4/385155/46 {+an edit, replacing tabs}
0
 
sam2929Author Commented:
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 |
0
 
PortletPaulfreelancerCommented:
-- 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 |

Open in new window

see: http://sqlfiddle.com/#!4/385155/51
0
 
PortletPaulfreelancerCommented:
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 |

Open in new window

Are there any further 'business rules' that need to be implemented?
0
 
sam2929Author Commented:
getting below error after adding

 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



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
0
 
PortletPaulfreelancerCommented:
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.
0
 
sam2929Author Commented:
when i do
where to_char(C.PAY_SCALE_END_DT,'YYYY-MM-DD') <> '9999-12-31' it looks like ok

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_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
0
 
PortletPaulfreelancerCommented:
CASE
        WHEN  MIN(C.PAY_SCALE_START_DT) < '99991231' AND
                    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

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)
0
 
PortletPaulfreelancerCommented:
Mmm, it might have to be:

CASE
        WHEN  LEAD(MAX(C.PAY_SCALE_END_DT),1) OVER (ORDER BY C.PAY_SCALE_END_DT DESC) < '99991231' AND
                    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
0
 
PortletPaulfreelancerCommented:
How is this going? Are we there yet?
0
 
PortletPaulfreelancerCommented:
Hi, thanks for the points - assume you have this solved now. Cheers, Paul
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

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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