Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print 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 = 84990So 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) result3from (    select id, res, row_number() over(partition by id order by result) seq    from yourtable ) dgroup by idorder 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                  Calculate the difference between the highest and lowest salaries and divide the difference by four and rounding the result to the nearest dollar. 29000 - 21000 = \$8,000/4  = \$2,000 (this is the width of each quarter   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 000Quarter 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. Active Posters