189 8069 5689
如下图，统计某一时间段请假类别为Kj_tx 积休假、Kj_nj年假的天数综合 并按列显示
成都创新互联是一家专注于成都网站建设、成都网站设计与策划设计,阜新网站建设哪家好?成都创新互联做网站,专注于网站建设十年,网设计领域的专业建站公司;建站业务涵盖:阜新等地区。阜新做网站价格咨询:13518219792
select
a.AlEmpID as 员工ID,
a.alsdate,
a.aledate,
AlTime=(
case when a.ALSTime=480 and a.ALETime=1050 then 1
when a.ALSTime=480 and a.ALETime=720 then 0.5
when a.ALSTime=840 and a.ALETime=1050 then 0.5
when a.ALSTime=870 and a.ALETime=1050 then 0.5
else 0 end) ,
a.ALFType as 请假类别
from kq_askleave a
where a.AlEmpID=734 and a.alsdate>='2018-03-01 00:00:00'
select
aa.员工ID,
max(d.Dept_lname) as 科室,
max(b.Emp_code) as 工号,
max(b.Emp_name) as 姓名,
max(b.Emp_zhiweiname) as 职位,
isnull(sum(case when aa.请假类别 ='Kj_nj' then aa.AlTime+AlDay end ),0) 年假,
isnull(sum(case when aa.请假类别 ='Kj_sj' then aa.AlTime+AlDay end ),0) 事假,
isnull(sum(case when aa.请假类别 ='Kj_bj' then aa.AlTime+AlDay end ),0) 病假,
isnull(sum(case when aa.请假类别 ='Kj_cj' then aa.AlTime+AlDay end ),0) 产假,
isnull(sum(case when aa.请假类别 ='Kj_hj' then aa.AlTime+AlDay end ),0) 婚假,
isnull(sum(case when aa.请假类别 ='Kj_tx' then aa.AlTime+AlDay end ),0) 积休假
from
(select
a.AlEmpID as 员工ID,
a.alsdate,
a.aledate,
AlDay=(
case when a.ALSTime=480 and a.ALETime=1050 then datediff(day,a.alsdate,a.aledate)+1
when a.ALSTime=480 and a.ALETime=720 then datediff(day,a.alsdate,a.aledate)
when a.ALSTime=840 and a.ALETime=1050 then datediff(day,a.alsdate,a.aledate)
when a.ALSTime=870 and a.ALETime=1050 then datediff(day,a.alsdate,a.aledate)
else 0 end) ,
AlTime=(
case when a.ALSTime=480 and a.ALETime=1050 then 0
when a.ALSTime=480 and a.ALETime=720 then 0.5
when a.ALSTime=840 and a.ALETime=1050 then 0.5
when a.ALSTime=870 and a.ALETime=1050 then 0.5
else 0 end) ,
a.ALFType as 请假类别
from kq_askleave a
where a.AlEmpID=734 and a.alsdate>='2016-11-01 00:00:00' and a.aledate<='2018-05-01 00:00:00') aa
left join employeeview b on aa.员工ID=b.emp_id
left join kq_altype c on aa.请假类别=c.kqaltcode
left join emp_department d on b.Dept_code=d.Dept_code
group by aa.员工ID
