统计所有员工节假日加班时间

fuxiaocaolove 2012-12-26 03:27:06

按月份统计节假日和周末加班
方法：sum（每个员工实际提交的工时—理论工时）

这是自己写的查询按月查询每个员工实际工时的语句
select

sr.id,
sr.full_name,
sum(PE.PRACTSUM/3600/8) act_days,-------实际工时
month(PP.PRSTART) start

FROM
srm_resources sr
inner join PRTIMESHEET pt on sr.id = pt.prresourceid
inner join PRTIMEPERIOD PP on Pt.PRTIMEPERIODID = PP.PRID
inner join pac_mnt_resources pmr on sr.unique_name = pmr.RESOURCE_CODE
inner join prtimeentry pe on pt.prid = pe.prtimesheetid
inner join prtypecode ptc on pe.prtypecodeid = ptc.prid

where
pp.prstart >= '2012-01-01'
and pp.prfinish <= '2013-01-01'
and pt.prstatus = 4
and (pmr.LOCATIONID = 'C1_BJ' or pmr.LOCATIONID = 'C1_sh')
and ptc.prid <>5000007
group by sr.id,sr.full_name,month(pp.prstart)

然后新建了一个日历表Corporate_Calendar标记所有的假期（holiday）和工作日（workday），用于统计每个月的工作日和节假日

例如1月份
实际工时的计算：先统计每个员工的工时，再将同一月份的工时汇总

理论工时的计算：分3种情况
1. 正常情况：员工的date_of_hire（入职日期）<2012-01-01 and date_of_termination(离职日期) = null or date_of_termination >2012-01-31,理论工时的计算： select count(*) days from Corporate_Calendar where holiday = 0 and month(date) = 1
2. 1月份入职：date_of_hire（入职日期）>2012-01-01, 理论工时的计算：select count(*) days from Corporate_Calendar where holiday = 0 and date between date_of_hire and 该月最后一天
3. 3.1月份离职：date_of_termination <2012-01-31, 理论工时的计算：select count(*) days from Corporate_Calendar where holiday = 0 and date between 该月第一天 and date_of_termination

有没有人告诉我大概怎么写sql或者存储过程，思路明白，不知道怎么下手

...全文