freedakgmail
34 lines
3.6 KiB
SQL
34 lines
3.6 KiB
SQL
TRUNCATE TABLE analytics.dim_employee;
|
|
INSERT INTO analytics.dim_employee (employee_id, employee_name, position, store_code, hire_date, leave_date, status, created_at, updated_at)
|
|
SELECT DISTINCT ON (employee_code)
|
|
employee_code, NULL,
|
|
CASE
|
|
WHEN position LIKE '店长%' OR position = '储备店长' OR position LIKE '见习经理%' OR position = '储备经理' THEN '店长'
|
|
WHEN position LIKE '副店%' THEN '副店长'
|
|
WHEN position LIKE '前厅经理%' OR position = '大堂经理' OR position = '服务主管' THEN '前厅经理'
|
|
WHEN position = '区经理' OR position LIKE '营运经理%' OR position = '营运助理' OR position LIKE '营运总监%' THEN '区经理'
|
|
WHEN position = '厨师长' OR position LIKE '厨师长%' OR position = '行政总厨' OR position LIKE '区厨%' OR position LIKE '大区总厨%' OR position LIKE '拉面区厨%' OR position LIKE '拉面总厨%' OR position = '研发总厨' OR position = '研发经理' OR position LIKE '配送中心%总厨%' OR position = '烤鸭总厨' OR position = '西餐总厨' THEN '厨师长'
|
|
WHEN position LIKE '拉面师%' OR position = '拉面' OR position LIKE '拉面主管%' OR position = '面工' OR position LIKE '面工%' OR position = '面点' OR position = '面点师' OR position = '打馕' THEN '拉面师'
|
|
WHEN position LIKE '厨师%' OR position = '炒锅' OR position = '砧板' OR position = '打荷' OR position = '上什' OR position = '蒸箱' OR position = '出品' OR position = '副厨' OR position = '明档师傅' OR position = '西餐' OR position = '锅底' THEN '厨师'
|
|
WHEN position LIKE '配菜师%' OR position = '配菜师' OR position = '切菜师' OR position LIKE '切菜师%' OR position = '切肉师' OR position = '砧板主管' THEN '配菜师'
|
|
WHEN position LIKE '凉菜%' OR position = '凉菜' THEN '凉菜师'
|
|
WHEN position LIKE '烧烤%' OR position = '烧烤师' OR position LIKE '烧烤工%' OR position = '烧烤师傅' OR position = '烤鸭师' THEN '烧烤师'
|
|
WHEN position LIKE '服务员%' OR position = '传菜员' OR position = '迎宾员' OR position = '吧员' THEN '服务员'
|
|
WHEN position = '收银员' OR position = '出纳主管' OR position LIKE '%出纳%' THEN '收银员'
|
|
WHEN position = '训练员' OR position LIKE '训练员%' OR position = '训练经理' THEN '训练员'
|
|
WHEN position LIKE '厨工%' OR position = '兼职工' OR position = '非全兼职工' OR position = '小时工' OR position = '计时工' THEN '厨工'
|
|
WHEN position = '保洁员' OR position = '保洁' THEN '保洁'
|
|
WHEN position = '洗碗' THEN '洗碗工'
|
|
WHEN position LIKE '%组员%' OR position LIKE '%组长%' THEN '中央厨房工'
|
|
WHEN position = '库房组员' OR position = '库房专员' OR position = '库房资深专员' THEN '库管'
|
|
WHEN position LIKE '副总%' OR position LIKE '执行总裁%' OR position LIKE '%总监%' OR position LIKE '%经理%' OR position LIKE '%主管%' OR position LIKE '%专员%' OR position LIKE '%助理%' OR position = '主管' THEN '管理岗'
|
|
ELSE '其他'
|
|
END,
|
|
NULL,
|
|
CASE WHEN hire_date ~ '^\d{4}-\d{2}-\d{2}$' THEN hire_date::date WHEN hire_date ~ '^\d{4}/\d{2}/\d{2}$' THEN to_date(hire_date, 'YYYY/MM/DD') ELSE NULL END,
|
|
CASE WHEN leave_date = '0' OR leave_date IS NULL OR leave_date = '' THEN NULL WHEN leave_date ~ '^\d{4}-\d{2}-\d{2}$' THEN leave_date::date WHEN leave_date ~ '^\d{4}/\d{2}/\d{2}$' THEN to_date(leave_date, 'YYYY/MM/DD') ELSE NULL END,
|
|
CASE WHEN leave_date = '0' OR leave_date IS NULL OR leave_date = '' THEN '在职' ELSE '离职' END,
|
|
now(), now()
|
|
FROM public.salary_detail_records
|
|
ORDER BY employee_code, leave_date DESC NULLS LAST;
|