The Quartile splitup Which trying to approach in Query Level .

Database

The Quartile splitup Which trying to approach in Query Level .

The Quartile Which trying to approach in Teradata Select Query Level .

PFB the Sample data for a set of OCG the annual salary and the employee count are taken in excel and the count is taken

The base SQl  Queried for the Quartile as per the given rule mentioned in the FS :

Sample Data :

OCG Group

Annual Salary

Employee Count

2

47,640.00

1

2

51,660.00

1

2

63,900.00

9

2

71,880.00

12

2

79,740.00

32

2

89,160.00

5

2

115,920.00

151

2

151,620.00

42

2

197,040.00

1

Total

254

The Base Querey is attached in the Document for the below calculation

To Calculate the width of each quarter:

highest and lowest salaries and divide the difference by four

(47,640.00 - 197,040.00 )/4 = 37,350.00

Checking the Lower Limit and the Upper limit :

the lower limit is the lowest salary of the employees in the occupational group 02 the employee ‘TV29CI00’ has the lowest 47,640.000 .

The upper limit is the lowest salary plus the amount determined              the width of each quarter

(i.e) 47,640 + 37,350 = 84990

So as per our Q1 Upper and lower limit it should be

The Employee count between the Range 47,640 between 84990  is 60 employees are falls under this criteria as per the above data given in the table but the split is done at the ocg level and the employee count is done at that level so it’s showing the Employee Count as 254 as shown in the below data .

As shown in the below data.

HRDC07_GVRMT_OCPTN_GRP_C

width_of_quarter

Q1_Lower_Limit

Q1_Upper_Limit

Count(Distinct(HRDR01_EMPLOYEE_ID_C))

2

37,350.00

47,640.00

84,990.00

254

The below technical points are the Blocks which we need to come out for Quartile scenario.

Because the representation are showing in the Column wise of all manipulated data like Width of the Quarter and the Upper and Lower limit of Q1 .

The Width of the Quarter is need to store as a temporary variable and we need to manipulate the upper and lower limit for Q1

The above mentioned point should be repeated for all the quarter as Q1 , Q2 Q3,Q4 as a iterative process like Loop .

The Column level Upper and lower limit range should compare, traverse and available in row wise for each quarter.

Expected Output

HRDC07_GVRMT_OCPTN_GRP_C

Quarter

Employee Count

2

Q1(47640 Between 84990

60

2

Q2(Q2 lower Limit to Q2 Upper Limit) 

 employee count

2

Q3

2

Q4

Things are tried:

We tried to put it as a variable in the horizontal table and cross tab the grouping of the employee count as per the split-up for Quarter of each OCG ‘s.

Tried the Repeat functions in the TD instead of loop to repeat the Q1 to Q4

Tried to analytic functions like Rank over partition and row_number() over(partition by id order by result)

Sudo Code :

select id,

   max(case when seq =1 then result end) result1,

   max(case when seq =2 then result end) result2,

   max(case when seq =3 then result end) result3

from

(

   select id, res, row_number() over(partition by id order by result) seq

   from yourtable

) d

group by id

order by id;

Checked for teradata functions that are equvilant  Pivot and transporse table  analytic function in the Oracle to show that in the column level.

The Base Querey is attached in the Document

Kindly give some suggestions on how to proceed further on the mentioned above technical points to come across form the technical Blocks for Quartile scenario.

Query:

SELECT

BHRDC07_GVRMT_OCPTN_GRP_VW.HRDC07_GVRMT_OCPTN_GRP_C,

/*CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '100000' ) then '$100,000' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '95000' ) then '$$95,000 - $99,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '90000' ) then '$90,000 - $94,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '85000' ) then '$85,000 - $89,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '80000' ) then '$80,000 - $84,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '75000' ) then '$75,000 - $79,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '70000' ) then '$70,000 - $74,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '65000' ) then '$65,000 - $69,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '60000' ) then '$60,000 - $64,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '55000' ) then '$55,000 - $59,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '50000' ) then '$50,000 - $54,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '45000' ) then '$45,000 - $49,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '40000' ) then '$40,000 - $44,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '35000' ) then '$35,000 - $39,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '30000' ) then '$30,000 - $34,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '25000' ) then '$25,000 - $29,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '20000' ) then '$20,000 - $19999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '15000' ) then '$15,000 - $19,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '10000' ) then '$10,000 - $14,999' ELSE

CASE WHEN (BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A >= '5000' ) then '$5,000 - $9,999' ELSE

'Under $5000' end end end end end end end end end end end end end end end end end end end end,*/

--BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A,

(max(BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A)-min(BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A))/4 as width_of_quarter,

min(BHRDJ02_EMP_JOB_VW.HRDJ02_SAL_ANNL_RT_A) as Q1_Lower_Limit,

(Q1_Lower_Limit + width_of_quarter) as Q1_Upper_Limit,

count ( distinct BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_ID_C)

from

BHRDR01_EMPLOYEE_VW INNER JOIN BHRDJ01_EMP_SERVICE_VW

ON (BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_K=BHRDJ01_EMP_SERVICE_VW.HRDR01_EMPLOYEE_K)

INNER JOIN BHRDJ02_EMP_JOB_VW

ON (BHRDJ02_EMP_JOB_VW.HRDJ01_EMP_SERVICE_K=BHRDJ01_EMP_SERVICE_VW.HRDJ01_EMP_SERVICE_K

AND BHRDJ02_EMP_JOB_VW.HRDJ02_DW_REC_STAT_C = 'A')

INNER JOIN BHRDV24_JOB_CODE_CLSFCN_R_VW BHRDV24_JOB_CODE_CLS_R_JOB

ON (BHRDV24_JOB_CODE_CLS_R_JOB.HRDC49_ISO_LANG_C='en_us'

AND BHRDV24_JOB_CODE_CLS_R_JOB.HRDC08_JOB_CD_CLSFCN_K=BHRDJ02_EMP_JOB_VW.HRDC08_JOB_CD_CLSFCN_K)

INNER JOIN BHRDC08_JOB_CODE_CLSFCN_VW BHRDC08_JOB_CODE_CLS_JOB

ON (BHRDC08_JOB_CODE_CLS_JOB.HRDC08_JOB_CD_CLSFCN_K=BHRDV24_JOB_CODE_CLS_R_JOB.HRDC08_SP_JOB_CD_CLSFCN_K)

INNER JOIN BHRDC06_GVRMT_OCPTN_CODE_VW

ON (BHRDC06_GVRMT_OCPTN_CODE_VW.HRDC06_GVRMT_OCPTN_K=BHRDC08_JOB_CODE_CLS_JOB.HRDC06_GVRMT_OCPTN_K)

INNER JOIN BHRDC07_GVRMT_OCPTN_GRP_VW

ON (BHRDC07_GVRMT_OCPTN_GRP_VW.HRDC07_GVRMT_OCPTN_GRP_K=BHRDC06_GVRMT_OCPTN_CODE_VW.HRDC07_GVRMT_OCPTN_GRP_K)

INNER JOIN BHRDV23_RGULTRY_REG_R_VW BHRDV23_RGULTRY_REG_R_JOB

ON (BHRDV23_RGULTRY_REG_R_JOB.HRDC49_ISO_LANG_C='en_us'

AND BHRDV23_RGULTRY_REG_R_JOB.HRDC53_RGULTRY_REG_K=BHRDJ02_EMP_JOB_VW.HRDC53_RGULTRY_REG_K)

INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_WRK_FR_TYPE

ON (BHRDV39_XLAT_VW_WRK_FR_TYPE.HRDC49_ISO_LANG_C='en_us'

AND BHRDV39_XLAT_VW_WRK_FR_TYPE.HRDCQ5_FIELD_N='EMPL_TYPE'

AND BHRDJ02_EMP_JOB_VW.HRDCQ5_WRK_FORCE_TYPE_K=BHRDV39_XLAT_VW_WRK_FR_TYPE.HRDCQ5_XLAT_ITEM_K)

INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_JOB_STAT

ON (BHRDV39_XLAT_VW_JOB_STAT.HRDC49_ISO_LANG_C='en_us'

AND BHRDV39_XLAT_VW_JOB_STAT.HRDCQ5_FIELD_N = 'EMPL_STATUS'

AND BHRDJ02_EMP_JOB_VW.HRDCQ5_JOB_STAT_K=BHRDV39_XLAT_VW_JOB_STAT.HRDCQ5_XLAT_ITEM_K)

INNER JOIN BHRDV57_EMPLMT_CLSFCN_R_VW BHRDV57_EMPLMT_CLSFCN_R_JOB

ON (BHRDJ02_EMP_JOB_VW.HRDC22_EMPLMT_CLSFCN_K=BHRDV57_EMPLMT_CLSFCN_R_JOB.HRDC22_EMPLMT_CLSFCN_K

AND BHRDV57_EMPLMT_CLSFCN_R_JOB.HRDC49_ISO_LANG_C='en_us')

Left Outer JOIN BHRDR64_EMP_OCPTN_SRVY_VW

ON (BHRDR64_EMP_OCPTN_SRVY_VW.HRDR01_EMPLOYEE_K=BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_K)

INNER JOIN BHRDR06_EMP_PERS_DATA_VW

ON (BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_K=BHRDR06_EMP_PERS_DATA_VW.HRDR01_EMPLOYEE_K

AND BHRDR06_EMP_PERS_DATA_VW.HRDR06_DW_REC_STAT_C = 'A')

INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_GENDER

ON (BHRDV39_XLAT_VW_GENDER.HRDCQ5_XLAT_ITEM_K=BHRDR06_EMP_PERS_DATA_VW.HRDCQ5_GENDER_K

AND BHRDV39_XLAT_VW_GENDER.HRDC49_ISO_LANG_C='en_US'

AND BHRDV39_XLAT_VW_GENDER.HRDCQ5_FIELD_N= 'SEX')

LEFT OUTER JOIN BHRDI01_ASMT_INFO_VW BHRDI01_ASMT_INFO_BASE

ON (BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_K=BHRDI01_ASMT_INFO_BASE.HRDR01_EMPLOYEE_K

AND BHRDI01_ASMT_INFO_BASE.HRDI01_DW_REC_STAT_C = 'A')

INNER JOIN BHRDF18_EMP_JOB_LINKAGE_VW

ON (BHRDJ02_EMP_JOB_VW.HRDJ02_EMP_JOB_K=BHRDF18_EMP_JOB_LINKAGE_VW.HRDJ02_EMP_JOB_K

AND BHRDF18_EMP_JOB_LINKAGE_VW.HRDF18_DW_REC_STAT_C = 'A')

INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_ISE_DESC

ON (BHRDV39_XLAT_VW_ISE_DESC.HRDCQ5_XLAT_ITEM_K=BHRDF18_EMP_JOB_LINKAGE_VW.HRDCQ5_ISE_STATUS_K

AND BHRDV39_XLAT_VW_ISE_DESC.HRDCQ5_FIELD_N='ISE_STATUS'

AND BHRDV39_XLAT_VW_ISE_DESC.HRDC49_ISO_LANG_C='en_US')

WHERE

( BHRDJ02_EMP_JOB_VW.HRDJ02_SRC_EFF_Y ) <= ('2014-07-01')

And ( BHRDJ02_EMP_JOB_VW.HRDJ02_DW_SRC_EFF_OUT_Y ) > ('2014-07-01')

AND ( ( BHRDV23_RGULTRY_REG_R_JOB.HRDC53_RGULTRY_REG_C ) IN ('CAN') )

AND ( ( BHRDV39_XLAT_VW_WRK_FR_TYPE.HRDCQ5_FIELD_VALUE_C ) in ('S',

'X') )

AND ( BHRDV39_XLAT_VW_JOB_STAT.HRDCQ5_FIELD_VALUE_C IN ('A',

'L','P') )

AND ( BHRDV57_EMPLMT_CLSFCN_R_JOB.HRDC22_EMPLMT_CLSFCN_C in ('STD',

'H','SUP') )

group by 1

Case Study:

                     The salaries of six typists of the Occupational Group “Clerical Personnel” are as listed:                   

                      Employee A - $29,000

                      Employee B -$27,000

                      Employee C - $26,500

                      Employee D - $25,500

                      Employee E - $24,000

                      Employee F - $21,000

                     Each Occupational Group contains four quarters. Quarter 4 always refers to the      

                     Highest earnings and Quarter 1 to the lowest. To calculate the salary quarter, 

                     employers must

 
 
 
 
 
 
  1. Calculate the difference between the highest and lowest salaries and divide the difference by four and rounding the result to the nearest dollar.
  2. 29000 - 21000 = $8,000/4  = $2,000 (this is the width of each quarter
  1. The limits of the first salary quarter are:

(a) the lower limit is the lowest salary of the employees in the occupational group

Employee F’s salary ($21,000) is the lowest.  It is therefore the bottom of quarter 1. 

(b) The upper limit is the lowest salary plus the amount determined in 1.

Add the width of $2 000 to $21 000 to obtain the top of quarter 1

 

Quarter 1:  $21 000 to $23 000

3. The limits of the second salary quarter are:

(c) the lower limit is the upper limit for the first salary quarter plus $1

$23,000 + $1 = S23, 001

(d) The upper limit is the lower limit plus the width (minus $1).

S23, 001 + $2,000 = $25,001 (minus $1)

 

Quarter 2:  S23, 001 to $25,000

 

Same calculation follows for Quarter 3 and 4

 

Quarter 3:  $25 001 - $27 000

Quarter 4:  $27 001 - $29 000

 

4. The employees would be grouped into the Salary Quarters according to their salaries

Q4 – Employee A

Q3 - Employee B, C, D

Q2 – Employee E

Q1 – Employee F

1 REPLY
Senior Apprentice

Re: The Quartile splitup Which trying to approach in Query Level .

I didn't read all that text, but it seems like your narration doesn't fit the definition of a quantile.

Might be a equally sized buckets instead, check WIDTH_BUCKET:

WIDTH_BUCKET(salary
,MAX(salary) OVER (PARTITION BY ocg)
,MIN(salary) OVER (PARTITION BY ocg) -1
,4)