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 :
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.
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.
Q1(47640 Between 84990
Q2(Q2 lower Limit to Q2 Upper Limit)
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 :
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
select id, res, row_number() over(partition by id order by result) seq
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.
/*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,*/
(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)
BHRDR01_EMPLOYEE_VW INNER JOIN BHRDJ01_EMP_SERVICE_VW
INNER JOIN BHRDJ02_EMP_JOB_VW
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
INNER JOIN BHRDC08_JOB_CODE_CLSFCN_VW BHRDC08_JOB_CODE_CLS_JOB
INNER JOIN BHRDC06_GVRMT_OCPTN_CODE_VW
INNER JOIN BHRDC07_GVRMT_OCPTN_GRP_VW
INNER JOIN BHRDV23_RGULTRY_REG_R_VW BHRDV23_RGULTRY_REG_R_JOB
INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_WRK_FR_TYPE
INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_JOB_STAT
AND BHRDV39_XLAT_VW_JOB_STAT.HRDCQ5_FIELD_N = 'EMPL_STATUS'
INNER JOIN BHRDV57_EMPLMT_CLSFCN_R_VW BHRDV57_EMPLMT_CLSFCN_R_JOB
Left Outer JOIN BHRDR64_EMP_OCPTN_SRVY_VW
INNER JOIN BHRDR06_EMP_PERS_DATA_VW
AND BHRDR06_EMP_PERS_DATA_VW.HRDR06_DW_REC_STAT_C = 'A')
INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_GENDER
AND BHRDV39_XLAT_VW_GENDER.HRDCQ5_FIELD_N= 'SEX')
LEFT OUTER JOIN BHRDI01_ASMT_INFO_VW BHRDI01_ASMT_INFO_BASE
AND BHRDI01_ASMT_INFO_BASE.HRDI01_DW_REC_STAT_C = 'A')
INNER JOIN BHRDF18_EMP_JOB_LINKAGE_VW
AND BHRDF18_EMP_JOB_LINKAGE_VW.HRDF18_DW_REC_STAT_C = 'A')
INNER JOIN BHRDV39_XLAT_ITEM_R_VW BHRDV39_XLAT_VW_ISE_DESC
( 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',
AND ( BHRDV39_XLAT_VW_JOB_STAT.HRDCQ5_FIELD_VALUE_C IN ('A',
AND ( BHRDV57_EMPLMT_CLSFCN_R_JOB.HRDC22_EMPLMT_CLSFCN_C in ('STD',
group by 1
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,
(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
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:
,MAX(salary) OVER (PARTITION BY ocg)
,MIN(salary) OVER (PARTITION BY ocg) -1