SQL 按天、周、月、季度、年汇总
工单量报表(by 日期,工单处理人,工单类型,SLA解决)
// 天
select a.day,sum(a.new) as 'new',sum(a.close) as 'close',a.ServiceStaff_2,a.servicetype
from(
select cast(createtime as date) as day,count(createtime) as 'new',0 as 'close',ServiceStaff_2,servicetype
from gongdan
group by cast (createtime as date),ServiceStaff_2,servicetype
union all
select cast(closetime as date) as day, 0 as 'new',count(closetime) as 'close',ServiceStaff_2,servicetype
from gongdan
group by cast (closetime as date),ServiceStaff_2,servicetype
)a
group by a.day,ServiceStaff_2,servicetype;
// 周
select a.Week,sum(a.new) as 'new',sum(a.close) as 'close',ServiceStaff_2,servicetype
from(
select week(createtime) as Week,count(createtime) as 'new',0 as 'close',ServiceStaff_2,servicetype
from gongdan
#where datediff(closetime,createtime) <= SLA
group by week(createtime),ServiceStaff_2,servicetype
union all
select week(createtime) as Week, 0 as 'new',count(closetime) as 'close',ServiceStaff_2,servicetype
from gongdan
#where datediff(closetime,createtime) <= SLA
group by week(createtime),ServiceStaff_2,servicetype
)a
group by a.Week,ServiceStaff_2,servicetype;
//月
select a.Month