lba query
unknown
sql
3 years ago
8.3 kB
6
Indexable
Never
drop table if exists proforma_lba_budget;
create temporary table proforma_lba_budget as (
select load_month
,hire_date
,effective_date
,date_part('year', hire_date) as hire_year
,emp_id
,upper(full_name) as full_name
,mappings.business_line_code
,mappings.business_unit_code
,mappings.market_code
,mappings.function_code
,project_code
,dim_bl.axis_mapping
,dim_bl.business_line_name
,dim_func.function_name
,dim_proj.product_descr
,mappings.reimbursable
from lba_mappings as mappings
left join dim_business_line as dim_bl
on dim_bl.business_line_code = mappings.business_line_code
left join dim_function as dim_func
on mappings.function_code = dim_func.function_code
left join dim_project as dim_proj
on dim_proj.project_id = mappings.project_code
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
drop table if exists consol_lba;
create temporary table consol_lba as (
select case when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.function_code = c.function_code and o.project_code = c.project_code then 'No Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.function_code = c.function_code then 'Project Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.project_code = c.project_code then 'Function Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.function_code = c.function_code and o.project_code = c.project_code then 'Market Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.market_code = c.market_code and o.function_code = c.function_code and o.project_code = c.project_code then 'BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.function_code = c.function_code and o.project_code = c.project_code then 'BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code then 'Project and Function Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.function_code = c.function_code then 'Project and Market Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.market_code = c.market_code and o.function_code = c.function_code then 'Project and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.function_code = c.function_code then 'Project and BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code and o.project_code = c.project_code then 'Function and Market Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.market_code = c.market_code and o.project_code = c.project_code then 'Function and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code and o.project_code = c.project_code then 'Function and BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.function_code = c.function_code and o.project_code = c.project_code then 'Market and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.function_code = c.function_code and o.project_code = c.project_code then 'Market and BL Change'
when o.emp_id = c.emp_id and o.market_code = c.market_code and o.function_code = c.function_code and o.project_code = c.project_code then 'BU and BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.business_unit_code = c.business_unit_code then 'Project, Function, and Market Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.market_code = c.market_code then 'Project, Function, and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.market_code = c.market_code then 'Project, Function, and BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code and o.project_code = c.project_code then 'Function, Market, and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code and o.project_code = c.project_code then 'Function, Market, and BL Change'
when o.emp_id = c.emp_id and o.function_code = c.function_code and o.project_code = c.project_code then 'Market, BU, and BL Change'
when o.emp_id = c.emp_id and o.business_line_code = c.business_line_code then 'Project, Function, Market, and BU Change'
when o.emp_id = c.emp_id and o.business_unit_code = c.business_unit_code then 'Project, Function, Market, and BL Change'
when o.emp_id = c.emp_id and o.project_code = c.project_code then 'Function, Market, BU, and BL Change'
when o.emp_id = c.emp_id then 'Full GL String Change'
when o.emp_id is null and c.hire_date >= '2022-01-01' then '2022 New Hire'
when o.emp_id is null and c.hire_date < '2022-01-01' and c.effective_date < '2021-12-01' then 'Pre-Dec''21 Transfer not in Budget'
when o.emp_id is null and c.hire_date < '2022-01-01' and c.effective_date between '2021-12-01' and '2021-12-31' then 'Dec''21 Transfer not in Budget'
when o.emp_id is null and c.hire_date < '2022-01-01' and c.effective_date >= '2022-01-01' and c.function_code in ('4902', '4917', '4923', '4927') then 'New 2022 Transfer into Core Legacy Function'
when o.emp_id is null and c.hire_date < '2022-01-01' and c.effective_date >= '2022-01-01' and c.function_code not in ('4902', '4917', '4923', '4927') then 'New 2022 Transfer into Core'
when o.emp_id is not null and c.emp_id is null then 'Attrited Heads'
else 'Other' end as status
,c.effective_date
,o.hire_date as original_hire_date
,c.hire_date as current_hire_date
,o.emp_id as original_emp_id
,c.emp_id as current_emp_id
,o.full_name as original_full_name
,c.full_name as current_full_name
,o.business_line_code as original_bl
,c.business_line_code as current_bl
,o.business_unit_code as original_bu
,c.business_unit_code as current_bu
,o.market_code as original_mkt
,c.market_code as current_mkt
,o.function_code as original_function
,c.function_code as current_function
,o.project_code as original_project
,c.project_code as current_project
,o.axis_mapping as original_axis
,c.axis_mapping as current_axis
,o.business_line_name as original_bl_name
,c.business_line_name as current_bl_name
,o.function_name as original_func_name
,c.function_name as current_func_name
,o.product_descr as original_product_desc
,c.product_descr as current_product_desc
,o.reimbursable as original_reimb
,c.reimbursable as current_reimb
from original_lba_budget as o
full join proforma_lba_budget as c
on o.emp_id = c.emp_id
where c.load_month = '2022-03-01'
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);
select status, count(*) as headcount
from consol_lba
group by 1
order by headcount desc
/*LBA Summary*/
select consol_lba.*,lba_budget.month_beginning, dim_account.account_name, lba_budget.amount
from consol_lba
left join lba_budget
on consol_lba.original_emp_id = lba_budget.emp_id
left join lba_mappings
on consol_lba.current_emp_id = lba_mappings.emp_id
left join dim_account
on dim_account.account_code = lba_budget.account_code
where lba_budget.month_beginning = '2022-03-01'