上传的图像用于项目: 'CST技术支持'
  1. CST技术支持
  2. SLAL2-2096

【和黄药业】SQL设计报表

XMLWord打印

    • Icon: Change Change
    • 解决结果: 完成
    • Icon: High High
    • 202103
    • 202103
    • H-和黄药业-Y2019081
    • PRO
    • hryl-uat
    • 基础通用功能
    • 隐藏

      sql:

      SELECT
      employee_id x_employee_id,
      min(schedule_date) x_schedule_begin_date,
      max(schedule_date) x_schedule_end_date,
      sum(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_total_duration
      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
      where
      to_char(leave_detail_view.schedule_date,'YYYY-MM')<=to_char(current_date::timestamp + '-1 month','YYYY-MM')
      and to_char(current_date::timestamp + '-1 month','YYYY-MM')>= to_char(current_date::timestamp + '-6 month','YYYY-MM')
      and
      hr_holiday_type.code in ('L012','L007','L024','L012_In')
      group by employee_id
      having
      sum(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) >=125

      显示
      sql: SELECT employee_id x_employee_id, min(schedule_date) x_schedule_begin_date, max(schedule_date) x_schedule_end_date, sum(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_total_duration 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 where to_char(leave_detail_view.schedule_date,'YYYY-MM')<=to_char(current_date::timestamp + '-1 month','YYYY-MM') and to_char(current_date::timestamp + '-1 month','YYYY-MM')>= to_char(current_date::timestamp + '-6 month','YYYY-MM') and hr_holiday_type.code in ('L012','L007','L024','L012_In') group by employee_id having sum(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) >=125

      统计截止上月月底，累计连续病假>=125天的员工名单

      条件1:属于连续病假员工，判断方式：
      前6个月申请带病字的假期累计大于等于125天，即认为是连续病假员工
      条件2：数据统计截止上月月底

      字段（）：employee_id（many2one）、工号、姓名、连续病假起始日期、连续病假天数

        1. 图片.png
          图片.png
          166 kB

            grant.yang 杨光磊
            jason.zhou 周子深
            表决:
            0 为这个问题表决

              创建日期:
              已更新:
              已解决: