SQLize | PHPize | SQLtest

DROP VIEW ert.execrec_pm_pxt ; CREATE VIEW ert.execrec_pm_pxt AS WITH main_cte as( With base_hc as ( SELECT distinct month_end_date , employee_name_current_preferred , emplid , employee_login ,concat('phonetool.amazon.com/users/', employee_login) AS Phonetool , business_unit_name , department_name , sim_org_level_number , reports_to_supervisor_employee_login , reports_to_supervisor_employee_name , sim_reports_to_level_2_employee_name , sim_reports_to_level_3_employee_name , sim_reports_to_level_4_employee_name , sim_reports_to_level_5_employee_name , sim_reports_to_level_6_employee_name , sim_reports_to_level_7_employee_name , sim_reports_to_level_8_employee_name , sim_reports_to_level_9_employee_name , sim_reports_to_level_10_employee_name , sim_reports_to_level_11_employee_name , sim_reports_to_level_2_employee_login , sim_reports_to_level_3_employee_login , sim_reports_to_level_4_employee_login , sim_reports_to_level_5_employee_login , sim_reports_to_level_6_employee_login , sim_reports_to_level_7_employee_login , sim_reports_to_level_8_employee_login , sim_reports_to_level_9_employee_login , sim_reports_to_level_10_employee_login , sim_reports_to_level_11_employee_login , job_level_name , job_family_name ,job_code , job_tech_indicator , CASE WHEN job_tech_indicator = 'Tech' THEN 'Tech' WHEN job_family_name IN ('FC', 'CS', 'PHYRTL', 'FOODPR', 'KITCHN', 'CXQO', 'FLDREP','VPOPS','VPCS', 'PHARM') THEN 'Field' ELSE 'Corp' END AS field_tech_corp , job_title_name , employee_business_title as business_title , location_city_name , location_state_name , location_country_name ,geo_region_name , job_level_dwell_time , CASE WHEN ROUND(job_level_dwell_time) BETWEEN 0 AND 12 THEN '0-12 Months' WHEN ROUND(job_level_dwell_time) BETWEEN 13 AND 24 THEN '13-24 Months' WHEN ROUND(job_level_dwell_time) BETWEEN 25 AND 36 THEN '25-36 Months' WHEN ROUND(job_level_dwell_time) BETWEEN 37 AND 48 THEN '37-48 Months' WHEN ROUND(job_level_dwell_time) BETWEEN 49 AND 60 THEN '49-60 Months' WHEN ROUND(job_level_dwell_time) > 60 THEN '60+ Months' END AS job_dwell_bucket , tenure as tenure_months , CASE WHEN ROUND(tenure_months) BETWEEN 0 AND 12 THEN '0-12 Months' WHEN ROUND(tenure_months) BETWEEN 13 AND 24 THEN '13-24 Months' WHEN ROUND(tenure_months) BETWEEN 25 AND 36 THEN '25-36 Months' WHEN ROUND(tenure_months) BETWEEN 37 AND 48 THEN '37-48 Months' WHEN ROUND(tenure_months) BETWEEN 49 AND 60 THEN '49-60 Months' WHEN ROUND(tenure_months) > 60 THEN '60+ Months' END AS tenure_months_bucket , job_last_hire_date , is_boomerang , employee_bar_raiser_flag , employee_gender AS employee_gender_description , employee_ethnicity_description --- as it is from talent central , CASE WHEN employee_gender_description = 'Female' OR employee_ethnicity_description NOT IN ('White', 'UK: White', 'CNTI') THEN 'Yes' ELSE 'No' END AS Diverse , employee_veteran_status FROM gdei.talent_central WHERE 1=1 AND snapshot_date = as_of_date --- pulling latest data AND active_headcount = 1 AND job_level_name :: int in (8,10) --- limiting for L8 and L10 population as suggested in SIM ticket AND sim_reports_to_level_2_employee_login = 'galettib' AND employee_worker_type = 'EMP' AND (employee_class_name ILIKE ('Regular%') OR employee_class_name IN('Fixed Term Contractor - EU')) -- added as per CR AND step_plan_flag='N'-- exclude step-plan employees ) , ---- below cte is for calculating the org size by levels and f/t/c for L8 and L10 population above org_hc as ( select distinct sim_reports_to_level_2_employee_login , sim_reports_to_level_3_employee_login , sim_reports_to_level_4_employee_login , sim_reports_to_level_5_employee_login , sim_reports_to_level_6_employee_login , sim_reports_to_level_7_employee_login , sim_reports_to_level_8_employee_login , sim_reports_to_level_9_employee_login , sim_reports_to_level_10_employee_login , sim_reports_to_level_11_employee_login , job_level_name , CASE WHEN job_tech_indicator = 'Tech' THEN 'Tech' WHEN job_family_name IN ('FC', 'CS', 'PHYRTL', 'FOODPR', 'KITCHN', 'CXQO', 'FLDREP','VPOPS','VPCS', 'PHARM') THEN 'Field' ELSE 'Corp' END AS field_tech_corp , sum(active_headcount) as org_hc --calculating hc by different cuts like org levels, job level, field/tech/corp etc. to calculate L4+ org, L7+ org and f/t/c hc and total hc in final select from gdei.talent_central where 1=1 AND snapshot_date = as_of_date -- latest data AND active_headcount = 1 AND sim_reports_to_level_2_employee_login = 'galettib' AND employee_worker_type = 'EMP' AND (employee_class_name ILIKE ('Regular%') OR employee_class_name IN('Fixed Term Contractor - EU')) -- added as per CR AND step_plan_flag='N'-- exclude step-plan employees group by 1,2,3,4,5,6,7,8,9,10,11,12 ) , ------ Below CTE is for finding the latest rating record for different years ---- OV_record as ( SELECT c1.emplid , c1.yr_nb AS year , c1.qtr_nm , c1.performance_overall_value , c1.employee_performance as performance_rating , c1.employee_perceived_potential as potential_rating , RANK() OVER (PARTITION BY emplid,yr_nb ORDER BY update_timestamp DESC) AS yr_tm_rnk FROM gtm.emp_calibrate_tier3 c1 WHERE 1 = 1 AND qtr_nm = 1 AND yr_nb IN ('2023','2022','2021') ) , -----below cte has all ratings for 2021------ OV_2021 as ( select * from OV_record where 1=1 and yr_tm_rnk = 1 and year = '2021' ) , -----below cte has all ratings for 2022------ OV_2022 as ( select * from OV_record where 1=1 and yr_tm_rnk = 1 and year = '2022' ) , -----below cte has all ratings for 2023------ OV_2023 as ( select * from OV_record where 1=1 and yr_tm_rnk = 1 and year = '2023' ), ----- below cte is has the self join on base table for finding the nested population ---------- nested_hc as ( select distinct b1.employee_login , b1.job_level_name , case when b1.job_level_name = b2.job_level_name then 1 else 0 end as nested from base_hc b1 left join base_hc b2 on b1.job_level_name = b2.job_level_name and b1.reports_to_supervisor_employee_login = b2.employee_login ) ------------------------------------- select distinct b.employee_name_current_preferred as employee_name , b.emplid as employee_id , b.employee_login ,b.Phonetool , b.business_unit_name , b.department_name , b.sim_org_level_number --- level from top , reports_to_supervisor_employee_name as supervisor_name , b.sim_reports_to_level_2_employee_name , b.sim_reports_to_level_3_employee_name , b.sim_reports_to_level_4_employee_name /* , b.sim_reports_to_level_5_employee_name , b.sim_reports_to_level_6_employee_name , b.sim_reports_to_level_7_employee_name , b.sim_reports_to_level_8_employee_name , b.sim_reports_to_level_9_employee_name , b.sim_reports_to_level_10_employee_name */ , b.reports_to_supervisor_employee_login as supervisor_login , b.sim_reports_to_level_2_employee_login , b.sim_reports_to_level_3_employee_login , b.sim_reports_to_level_4_employee_login /* , b.sim_reports_to_level_5_employee_login , b.sim_reports_to_level_6_employee_login , b.sim_reports_to_level_7_employee_login , b.sim_reports_to_level_8_employee_login , b.sim_reports_to_level_9_employee_login , b.sim_reports_to_level_10_employee_login */ , b.job_title_name , b.business_title , b.job_level_name , b.job_family_name , b.job_code , b.job_tech_indicator , b.field_tech_corp --, b.job_title_name --,b.business_title , b.location_city_name , b.location_state_name , b.location_country_name ,b.geo_region_name , (case when b.sim_org_level_number >0 then b.sim_org_level_number -2 else 0 end) as stacking_from_org_level_2 --- no of layers from Doug , CASE WHEN b.job_level_name = n3.job_level_name AND b.sim_reports_to_level_3_employee_login = n3.employee_login THEN sim_org_level_number :: int - 3 WHEN b.job_level_name = n4.job_level_name AND b.sim_reports_to_level_4_employee_login = n4.employee_login THEN sim_org_level_number :: int - 4 WHEN b.job_level_name = n5.job_level_name AND b.sim_reports_to_level_5_employee_login = n5.employee_login THEN sim_org_level_number :: int - 5 WHEN b.job_level_name = n6.job_level_name AND b.sim_reports_to_level_6_employee_login = n6.employee_login THEN sim_org_level_number :: int - 6 WHEN b.job_level_name = n7.job_level_name AND b.sim_reports_to_level_7_employee_login = n7.employee_login THEN sim_org_level_number :: int - 7 WHEN b.job_level_name = n8.job_level_name AND b.sim_reports_to_level_8_employee_login = n8.employee_login THEN sim_org_level_number :: int - 8 WHEN b.job_level_name = n9.job_level_name AND b.sim_reports_to_level_9_employee_login = n9.employee_login THEN sim_org_level_number :: int - 9 WHEN b.job_level_name = n10.job_level_name AND b.sim_reports_to_level_10_employee_login = n10.employee_login THEN sim_org_level_number :: int - 10 WHEN b.job_level_name = n10.job_level_name AND b.sim_reports_to_level_11_employee_login = n11.employee_login THEN sim_org_level_number :: int - 11 else 0 end as nested_layers_above , b.job_level_dwell_time , b.job_dwell_bucket , b.tenure_months , b.tenure_months_bucket , cast(b.job_last_hire_date as date) , b.employee_gender_description , b.employee_ethnicity_description , b.Diverse , b.employee_veteran_status , b.is_boomerang , b.employee_bar_raiser_flag --- OV, performance and potential ratings for three years including the latest , ov1.performance_overall_value as OV_rating_2021 , ov2.performance_overall_value as OV_rating_2022 , ov3.performance_overall_value as OV_rating_2023 , ov1.performance_rating as performance_rating_2021 , ov2.performance_rating as performance_rating_2022 , ov3.performance_rating as performance_rating_2023 , ov1.potential_rating as potential_rating_2021 , ov2.potential_rating as potential_rating_2022 , ov3.potential_rating as potential_rating_2023 , sum(case when o.job_level_name:: int >=4 then o.org_hc else 0 end ) as l4_plus_hc, sum(case when o.job_level_name:: int >=7 then o.org_hc else 0 end ) as l7_plus_hc, sum(case when o.field_tech_corp = 'Field' then o.org_hc else 0 end) as field_hc, sum(case when o.field_tech_corp = 'Corp' then o.org_hc else 0 end) as corp_hc, sum(case when o.field_tech_corp = 'Tech' then o.org_hc else 0 end) as tech_hc, sum(o.org_hc) as total_org_hc from base_hc b left join nested_hc n3 on b.sim_reports_to_level_3_employee_login = n3.employee_login left join nested_hc n4 on b.sim_reports_to_level_4_employee_login = n4.employee_login left join nested_hc n5 on b.sim_reports_to_level_5_employee_login = n5.employee_login left join nested_hc n6 on b.sim_reports_to_level_6_employee_login = n6.employee_login left join nested_hc n7 on b.sim_reports_to_level_7_employee_login = n7.employee_login left join nested_hc n8 on b.sim_reports_to_level_8_employee_login = n8.employee_login left join nested_hc n9 on b.sim_reports_to_level_9_employee_login = n9.employee_login left join nested_hc n10 on b.sim_reports_to_level_10_employee_login = n10.employee_login left join nested_hc n11 on b.sim_reports_to_level_11_employee_login = n11.employee_login left join ov_2021 ov1 on b.emplid = ov1.emplid left join ov_2022 ov2 on b.emplid = ov2.emplid left join ov_2023 ov3 on b.emplid = ov3.emplid left join org_hc o on ( (b.employee_login = o.sim_reports_to_level_2_employee_login) or (b.employee_login = o.sim_reports_to_level_3_employee_login) or (b.employee_login = o.sim_reports_to_level_4_employee_login) or (b.employee_login = o.sim_reports_to_level_5_employee_login) or (b.employee_login = o.sim_reports_to_level_6_employee_login) or (b.employee_login = o.sim_reports_to_level_7_employee_login) or (b.employee_login = o.sim_reports_to_level_8_employee_login) or (b.employee_login = o.sim_reports_to_level_9_employee_login) or (b.employee_login = o.sim_reports_to_level_10_employee_login) or (b.employee_login = o.sim_reports_to_level_11_employee_login) ) WHERE (sim_reports_to_level_3_employee_name != 'Foster,Michael' AND b.employee_login != 'mfosa') AND ( sim_reports_to_level_4_employee_name != 'Vanover,Brian D' AND b.employee_login != 'vanoverb' ) group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54) , job_history AS ( SELECT * FROM ( SELECT * , rank() OVER (PARTITION BY emplid ORDER BY month_end_date ASC) as outer_rank FROM( SELECT DISTINCT emplid, job_code, employee_business_title, month_end_date , rank () OVER (PARTITION BY emplid, job_code ORDER BY month_end_date DESC) FROM gdei.talent_central) WHERE rank = 1 ) ORDER BY emplid,outer_rank ) SELECT m.*--, LISTAGG (h.employee_business_title, ','),LISTAGG (h.job_code, ',') SELECT m.*, LISTAGG (h.employee_business_title, ',') AS emp_title_history,LISTAGG (h.job_code, ',') AS job_code_history FROM main_cte m --LEFT JOIN job_history h --ON m.employee_id = h.emplid LEFT JOIN job_history h ON m.employee_id = h.emplid GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54 WITH NO SCHEMA BINDING ;