All Forums Database
Raghunath7789 4 posts Joined 07/14
16 Sep 2014
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_SER VICE_VW.HRDR01_EMPLOYEE_K)
INNER JOIN BHRDJ02_EMP_JOB_VW
ON (BHRDJ02_EMP_JOB_VW.HRDJ01_EMP_SERVICE_K=BHRDJ01_EMP_S ERVICE_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=BHRD J02_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=BHRDV 24_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=BHRD C08_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=B HRDC06_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_XLA T_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_J OB_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_EMP LMT_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_E MPLOYEE_VW.HRDR01_EMPLOYEE_K)
INNER JOIN BHRDR06_EMP_PERS_DATA_VW
ON (BHRDR01_EMPLOYEE_VW.HRDR01_EMPLOYEE_K=BHRDR06_EMP_PER S_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_IN FO_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_L INKAGE_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_E MP_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
 

dnoeth 4628 posts Joined 11/04
16 Sep 2014

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)

 

Dieter

You must sign in to leave a comment.