-
Change
-
解决结果: 完成
-
Block
-
无
-
H-和黄药业-Y2019081
-
和黄
-
时间管理
-
隐藏
休假记录明细分析new：
SELECT
employee_id as x_employee_id,
employee_number AS X_employee_number,
employee_name AS x_employee_name ,
leave_type AS x_leave_type_id,
display_holiday_type AS x_display_holiday_type,
hr_holiday_type.name AS x_leave_type,
schedule_date AS x_schedule_date,
case when product_uom.name='Day(s)' then leave_detail_view.duration when product_uom.name='Hour(s)' then leave_detail_view.duration/8.0 else 0 END x_duration ,
product_uom_id AS x_product_uom_id,
product_uom.name AS x_product_uom,
left(to_char(schedule_date,'YYYY-MM'),4) as x_year,
case when right(to_char(schedule_date,'YYYY-MM'),2) in ('01','02','03','04','05','06') then '上半年'
when right(to_char(schedule_date,'YYYY-MM'),2) in ('07','08','09','10','11','12') then '下半年' end
x_schedule_date1FROM leave_detail_view
left join product_uom ON leave_detail_view.product_uom_id=product_uom.id
left join hr_holiday_type ON leave_detail_view.leave_type=hr_holiday_type.id休假汇总报表new1:
select x_employee_id,
x_year as x_year ,
x_schedule_date1 as x_schedule_date1,
sum(case x_leave_type_id when '25' THEN x_duration else 0 end )as x_L025,
sum(case x_leave_type_id when '26' THEN x_duration else 0 end )as x_L026,
sum(case x_leave_type_id when '28' THEN x_duration else 0 end)as x_L027,
sum(case x_leave_type_id when '29' THEN x_duration else 0 end)as x_L028,
sum(case x_leave_type_id when '38' THEN x_duration else 0 end)as x_L032,
sum(case x_leave_type_id when '39' THEN x_duration else 0 end)as x_L033,
sum(case x_leave_type_id when '40' THEN x_duration else 0 end)as x_L034,
sum(case x_leave_type_id when '1' THEN x_duration else 0 end)as x_L001,
sum(case x_leave_type_id when '2' THEN x_duration else 0 end)as x_L002,
sum(case x_leave_type_id when '21' THEN x_duration else 0 end)as x_L021,
sum(case x_leave_type_id when '3' THEN x_duration else 0 end)as x_L003,
sum(case x_leave_type_id when '4' THEN x_duration else 0 end)as x_L004,
sum(case x_leave_type_id when '5' THEN x_duration else 0 end)as x_L005,
sum(case x_leave_type_id when '6' THEN x_duration else 0 end)as x_L006,
sum(case x_leave_type_id when '7' THEN x_duration else 0 end)as x_L007,
sum(case x_leave_type_id when '24' THEN x_duration else 0 end)as x_L024,
sum(case x_leave_type_id when '8' THEN x_duration else 0 end)as x_L008,
sum(case x_leave_type_id when '9' THEN x_duration else 0 end)as x_L009,
sum(case when x_leave_type_id='9' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L009_1,
sum(case x_leave_type_id when '10' THEN x_duration else 0 end)as x_L010,
sum(case when x_leave_type_id='10' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L010_1,
sum(case x_leave_type_id when '11' THEN x_duration else 0 end)as x_L011,
sum(case when x_leave_type_id= '11' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L011_1,
sum(case x_leave_type_id when '12' THEN x_duration else 0 end)as x_L012,
sum(case x_leave_type_id when '13' THEN x_duration else 0 end)as x_L013,
sum(case x_leave_type_id when '37' THEN x_duration else 0 end)as x_L031,
sum(case x_leave_type_id when '47' THEN x_duration else 0 end)as x_L012_In,
sum(case x_leave_type_id when '14' THEN x_duration else 0 end)as x_L014,
sum(case x_leave_type_id when '15' THEN x_duration else 0 end)as x_L015,
sum(case x_leave_type_id when '16' THEN x_duration else 0 end)as x_L016,
sum(case x_leave_type_id when '17' THEN x_duration else 0 end)as x_L017,
sum(case x_leave_type_id when '18' THEN x_duration else 0 end)as x_L018,
sum(case when x_leave_type_id='14' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L014_1,
sum(case when x_leave_type_id='15' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L015_1,
sum(case when x_leave_type_id='16' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L016_1,
sum(case when x_leave_type_id='17' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L017_1,
sum(case when x_leave_type_id='18' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L018_1,
sum(case x_leave_type_id when '22' THEN x_duration else 0 end)as x_L022,
sum(case x_leave_type_id when '23' THEN x_duration else 0 end)as x_L023,
sum(case x_leave_type_id when '36' THEN x_duration else 0 end)as x_L030FROM x_bi_sql_view_leave_detail_analysis_new
group by x_employee_id,x_year,x_schedule_date1union all
select x_employee_id,
x_year as x_year ,
case when x_schedule_date1 !='' then '全年' else '' end as x_schedule_date1,
sum(case x_leave_type_id when '25' THEN x_duration else 0 end )as x_L025,
sum(case x_leave_type_id when '26' THEN x_duration else 0 end )as x_L026,
sum(case x_leave_type_id when '28' THEN x_duration else 0 end)as x_L027,
sum(case x_leave_type_id when '29' THEN x_duration else 0 end)as x_L028,
sum(case x_leave_type_id when '38' THEN x_duration else 0 end)as x_L032,
sum(case x_leave_type_id when '39' THEN x_duration else 0 end)as x_L033,
sum(case x_leave_type_id when '40' THEN x_duration else 0 end)as x_L034,
sum(case x_leave_type_id when '1' THEN x_duration else 0 end)as x_L001,
sum(case x_leave_type_id when '2' THEN x_duration else 0 end)as x_L002,
sum(case x_leave_type_id when '21' THEN x_duration else 0 end)as x_L021,
sum(case x_leave_type_id when '3' THEN x_duration else 0 end)as x_L003,
sum(case x_leave_type_id when '4' THEN x_duration else 0 end)as x_L004,
sum(case x_leave_type_id when '5' THEN x_duration else 0 end)as x_L005,
sum(case x_leave_type_id when '6' THEN x_duration else 0 end)as x_L006,
sum(case x_leave_type_id when '7' THEN x_duration else 0 end)as x_L007,
sum(case x_leave_type_id when '24' THEN x_duration else 0 end)as x_L024,
sum(case x_leave_type_id when '8' THEN x_duration else 0 end)as x_L008,
sum(case x_leave_type_id when '9' THEN x_duration else 0 end)as x_L009,
sum(case when x_leave_type_id='9' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L009_1,
sum(case x_leave_type_id when '10' THEN x_duration else 0 end)as x_L010,
sum(case when x_leave_type_id='10' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L010_1,
sum(case x_leave_type_id when '11' THEN x_duration else 0 end)as x_L011,
sum(case when x_leave_type_id= '11' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L011_1,
sum(case x_leave_type_id when '12' THEN x_duration else 0 end)as x_L012,
sum(case x_leave_type_id when '13' THEN x_duration else 0 end)as x_L013,
sum(case x_leave_type_id when '37' THEN x_duration else 0 end)as x_L031,
sum(case x_leave_type_id when '47' THEN x_duration else 0 end)as x_L012_In,
sum(case x_leave_type_id when '14' THEN x_duration else 0 end)as x_L014,
sum(case x_leave_type_id when '15' THEN x_duration else 0 end)as x_L015,
sum(case x_leave_type_id when '16' THEN x_duration else 0 end)as x_L016,
sum(case x_leave_type_id when '17' THEN x_duration else 0 end)as x_L017,
sum(case x_leave_type_id when '18' THEN x_duration else 0 end)as x_L018,
sum(case when x_leave_type_id='14' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L014_1,
sum(case when x_leave_type_id='15' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L015_1,
sum(case when x_leave_type_id='16' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L016_1,
sum(case when x_leave_type_id='17' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L017_1,
sum(case when x_leave_type_id='18' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L018_1,
sum(case x_leave_type_id when '22' THEN x_duration else 0 end)as x_L022,
sum(case x_leave_type_id when '23' THEN x_duration else 0 end)as x_L023,
sum(case x_leave_type_id when '36' THEN x_duration else 0 end)as x_L030FROM x_bi_sql_view_leave_detail_analysis_new
group by x_employee_id,x_year,case when x_schedule_date1 !='' then '全年' else '' end显示休假记录明细分析new： SELECT employee_id as x_employee_id, employee_number AS X_employee_number, employee_name AS x_employee_name , leave_type AS x_leave_type_id, display_holiday_type AS x_display_holiday_type, hr_holiday_type.name AS x_leave_type, schedule_date AS x_schedule_date, case when product_uom.name='Day(s)' then leave_detail_view.duration when product_uom.name='Hour(s)' then leave_detail_view.duration/8.0 else 0 END x_duration , product_uom_id AS x_product_uom_id, product_uom.name AS x_product_uom, left(to_char(schedule_date,'YYYY-MM'),4) as x_year, case when right(to_char(schedule_date,'YYYY-MM'),2) in ('01','02','03','04','05','06') then '上半年' when right(to_char(schedule_date,'YYYY-MM'),2) in ('07','08','09','10','11','12') then '下半年' end x_schedule_date1 FROM leave_detail_view left join product_uom ON leave_detail_view.product_uom_id=product_uom.id left join hr_holiday_type ON leave_detail_view.leave_type=hr_holiday_type.id 休假汇总报表new1: select x_employee_id, x_year as x_year , x_schedule_date1 as x_schedule_date1, sum(case x_leave_type_id when '25' THEN x_duration else 0 end )as x_L025, sum(case x_leave_type_id when '26' THEN x_duration else 0 end )as x_L026, sum(case x_leave_type_id when '28' THEN x_duration else 0 end)as x_L027, sum(case x_leave_type_id when '29' THEN x_duration else 0 end)as x_L028, sum(case x_leave_type_id when '38' THEN x_duration else 0 end)as x_L032, sum(case x_leave_type_id when '39' THEN x_duration else 0 end)as x_L033, sum(case x_leave_type_id when '40' THEN x_duration else 0 end)as x_L034, sum(case x_leave_type_id when '1' THEN x_duration else 0 end)as x_L001, sum(case x_leave_type_id when '2' THEN x_duration else 0 end)as x_L002, sum(case x_leave_type_id when '21' THEN x_duration else 0 end)as x_L021, sum(case x_leave_type_id when '3' THEN x_duration else 0 end)as x_L003, sum(case x_leave_type_id when '4' THEN x_duration else 0 end)as x_L004, sum(case x_leave_type_id when '5' THEN x_duration else 0 end)as x_L005, sum(case x_leave_type_id when '6' THEN x_duration else 0 end)as x_L006, sum(case x_leave_type_id when '7' THEN x_duration else 0 end)as x_L007, sum(case x_leave_type_id when '24' THEN x_duration else 0 end)as x_L024, sum(case x_leave_type_id when '8' THEN x_duration else 0 end)as x_L008, sum(case x_leave_type_id when '9' THEN x_duration else 0 end)as x_L009, sum(case when x_leave_type_id='9' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L009_1, sum(case x_leave_type_id when '10' THEN x_duration else 0 end)as x_L010, sum(case when x_leave_type_id='10' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L010_1, sum(case x_leave_type_id when '11' THEN x_duration else 0 end)as x_L011, sum(case when x_leave_type_id= '11' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L011_1, sum(case x_leave_type_id when '12' THEN x_duration else 0 end)as x_L012, sum(case x_leave_type_id when '13' THEN x_duration else 0 end)as x_L013, sum(case x_leave_type_id when '37' THEN x_duration else 0 end)as x_L031, sum(case x_leave_type_id when '47' THEN x_duration else 0 end)as x_L012_In, sum(case x_leave_type_id when '14' THEN x_duration else 0 end)as x_L014, sum(case x_leave_type_id when '15' THEN x_duration else 0 end)as x_L015, sum(case x_leave_type_id when '16' THEN x_duration else 0 end)as x_L016, sum(case x_leave_type_id when '17' THEN x_duration else 0 end)as x_L017, sum(case x_leave_type_id when '18' THEN x_duration else 0 end)as x_L018, sum(case when x_leave_type_id='14' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L014_1, sum(case when x_leave_type_id='15' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L015_1, sum(case when x_leave_type_id='16' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L016_1, sum(case when x_leave_type_id='17' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L017_1, sum(case when x_leave_type_id='18' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L018_1, sum(case x_leave_type_id when '22' THEN x_duration else 0 end)as x_L022, sum(case x_leave_type_id when '23' THEN x_duration else 0 end)as x_L023, sum(case x_leave_type_id when '36' THEN x_duration else 0 end)as x_L030 FROM x_bi_sql_view_leave_detail_analysis_new group by x_employee_id,x_year,x_schedule_date1 union all select x_employee_id, x_year as x_year , case when x_schedule_date1 !='' then '全年' else '' end as x_schedule_date1, sum(case x_leave_type_id when '25' THEN x_duration else 0 end )as x_L025, sum(case x_leave_type_id when '26' THEN x_duration else 0 end )as x_L026, sum(case x_leave_type_id when '28' THEN x_duration else 0 end)as x_L027, sum(case x_leave_type_id when '29' THEN x_duration else 0 end)as x_L028, sum(case x_leave_type_id when '38' THEN x_duration else 0 end)as x_L032, sum(case x_leave_type_id when '39' THEN x_duration else 0 end)as x_L033, sum(case x_leave_type_id when '40' THEN x_duration else 0 end)as x_L034, sum(case x_leave_type_id when '1' THEN x_duration else 0 end)as x_L001, sum(case x_leave_type_id when '2' THEN x_duration else 0 end)as x_L002, sum(case x_leave_type_id when '21' THEN x_duration else 0 end)as x_L021, sum(case x_leave_type_id when '3' THEN x_duration else 0 end)as x_L003, sum(case x_leave_type_id when '4' THEN x_duration else 0 end)as x_L004, sum(case x_leave_type_id when '5' THEN x_duration else 0 end)as x_L005, sum(case x_leave_type_id when '6' THEN x_duration else 0 end)as x_L006, sum(case x_leave_type_id when '7' THEN x_duration else 0 end)as x_L007, sum(case x_leave_type_id when '24' THEN x_duration else 0 end)as x_L024, sum(case x_leave_type_id when '8' THEN x_duration else 0 end)as x_L008, sum(case x_leave_type_id when '9' THEN x_duration else 0 end)as x_L009, sum(case when x_leave_type_id='9' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L009_1, sum(case x_leave_type_id when '10' THEN x_duration else 0 end)as x_L010, sum(case when x_leave_type_id='10' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L010_1, sum(case x_leave_type_id when '11' THEN x_duration else 0 end)as x_L011, sum(case when x_leave_type_id= '11' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L011_1, sum(case x_leave_type_id when '12' THEN x_duration else 0 end)as x_L012, sum(case x_leave_type_id when '13' THEN x_duration else 0 end)as x_L013, sum(case x_leave_type_id when '37' THEN x_duration else 0 end)as x_L031, sum(case x_leave_type_id when '47' THEN x_duration else 0 end)as x_L012_In, sum(case x_leave_type_id when '14' THEN x_duration else 0 end)as x_L014, sum(case x_leave_type_id when '15' THEN x_duration else 0 end)as x_L015, sum(case x_leave_type_id when '16' THEN x_duration else 0 end)as x_L016, sum(case x_leave_type_id when '17' THEN x_duration else 0 end)as x_L017, sum(case x_leave_type_id when '18' THEN x_duration else 0 end)as x_L018, sum(case when x_leave_type_id='14' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L014_1, sum(case when x_leave_type_id='15' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L015_1, sum(case when x_leave_type_id='16' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L016_1, sum(case when x_leave_type_id='17' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L017_1, sum(case when x_leave_type_id='18' and x_display_holiday_type !='day_off' THEN x_duration else 0 end)as x_L018_1, sum(case x_leave_type_id when '22' THEN x_duration else 0 end)as x_L022, sum(case x_leave_type_id when '23' THEN x_duration else 0 end)as x_L023, sum(case x_leave_type_id when '36' THEN x_duration else 0 end)as x_L030 FROM x_bi_sql_view_leave_detail_analysis_new group by x_employee_id,x_year,case when x_schedule_date1 !='' then '全年' else '' end
之前这张报表抓取的是休假明细，对于婚假只能抓取到的是日历天，客户需要对于假期类型显示两列，一个是日历天，一个是计薪天
