This repository has been archived by the owner on Oct 10, 2018. It is now read-only.
forked from GabeHorwitz/SQL_Queries
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Dashboard_Workers
52 lines (52 loc) · 3.58 KB
/
Dashboard_Workers
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
with qIn as
(
select
case when ww.employee_id is not null then ww.flow else 1 end flow,
'In'::text flow_type,
ww.effective_date flow_date,
w.hire_date business_date,
date_part('month', w.hire_date)::int "month",
date_part('year', w.hire_date)::int "year",
m.who_is_your_manager,
l."key" "location",
cc.new_hierarchy,
w.*,
split_part(w.cost_center_hierarchy,' ',1) hierarchy_level_2,
case when w.region_finance_hierarchy_tag is null then mra.region else w.region_finance_hierarchy_tag end hierarchy_regional_new
from workday.workers w
left join public.missing_regional_assignments mra on mra.employee_id = w.employee_id
left join (select t.employee_id, 1 flow, t.effective_date from workday.transactional t where t.business_process_name = 'Hire') ww on w.employee_id = ww.employee_id
--left join (select t.employee_id, count(*) flow from workday.transactional t where t.business_process_name = 'Hire' group by t.employee_id having count(*) > 1) ww on w.employee_id = ww.employee_id
left join public.manager m on w.supervisory_organization = m.supervisory_organization
left join public.location l on l.location = w.building_location
left join public.cost_center cc on cc.cost_center_name = w.cost_center_name
--left join public.regional_mapping mm on mm.hierarchy_territory_assignment = w.hierarchy_territory_assignment
--where worker_type = 'Employee' and (employee_type = 'Regular' or employee_type = 'Fixed Term')
),
qOut as
(
select
case when ww.employee_id is not null then ww.flow else -1 end flow,
'Out'::text flow_type,
ww.effective_date flow_date,
w.termination_date business_date,
date_part('month', w.termination_date)::int "month",
date_part('year', w.termination_date)::int "year",
m.who_is_your_manager,
l."key" "location",
cc.new_hierarchy,
w.*,
split_part(w.cost_center_hierarchy,' ',1) hierarchy_level_2,
case when w.region_finance_hierarchy_tag is null then mra.region else w.region_finance_hierarchy_tag end hierarchy_regional_new
from workday.workers w
left join public.missing_regional_assignments mra on mra.employee_id = w.employee_id
left join (select t.employee_id, -1 flow, t.effective_date from workday.transactional t where t.business_process_name = 'Termination') ww on w.employee_id = ww.employee_id
--left join (select t.employee_id, -count(*) flow from workday.transactional t where t.business_process_name = 'Termination' group by t.employee_id having count(*) > 1) ww on w.employee_id = ww.employee_id
left join public.manager m on w.supervisory_organization = m.supervisory_organization
left join public.location l on l.location = w.building_location
left join public.cost_center cc on cc.cost_center_name = w.cost_center_name
--left join public.regional_mapping mm on mm.hierarchy_territory_assignment = w.hierarchy_territory_assignment
where termination_date is not null -- and worker_type = 'Employee' and (employee_type = 'Regular' or employee_type = 'Fixed Term')
)
select * from qIn union all
select * from qOut