SQL mark

// 天
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