set@schoolid=41;
select l.StartTime,l.EndTime,c.EntranceYear as 入学级,cg.Grade as 年级,c.ClassName as 班级名称,
s.CName as 学生,
st.StuNumber as 学号,
case l.LeaveType when 1 then '事假' when 2 then '病假' end as 请假类型,
l.Reason as 请假理由,
case l.ApproveStatus when 0 then '请假申请中' when 1 then '审批通过'
when 2 then '审批驳回' when 3 then '撤销审批中'
when 4 then '缩假审批中' when 5 then '已撤销'
end as 请假状态,
o.DisplayName as 审批人,
l.ApproveTime as 审批时间,
l.CreateTime as 审批时间2
from leaverecord l
left join class c on l.ClassId=c.Id
left join studentinfo s on s.id = l.StudentId
LEFT JOIN class_grade cg on cg.ClassId=c.Id
LEFT JOIN orguser o on o.id = l.ApproveOrgUserId
left join stustatusinfo st on st.StuInfoId = s.id
where l.SchoolId=@schoolid#校区
and l.LeaveType=2 #请假类型 1事假 2病假
and l.ApproveStatus=1#0请假申请中 1审批通过 2审批驳回3撤销审批中4缩假审批中5已撤销
and c.id=282#班级id
and ((l.EndTime <='2017-12-12 23:59:00' and l.StartTime >='2017-12-05 00:00:00')
or (l.EndTime >='2017-12-05 00:00:00' and l.StartTime <='2017-12-05 00:00:00')
or (l.EndTime >='2017-12-12 23:59:00' and l.StartTime <='2017-12-12 23:59:00'))
GROUP BY l.StartTime ,l.EndTime
ORDER BY l.StartTime ,l.EndTime asc, l.CreateTime desc;
