select t1.tagents_workersid,
       t1.tenterprise_userid,
       t1.tagentsid,
       t1.fname,
       t1.frole,
       t1.fphonenumber,
       t1.ftotalscore,
       t1.fscore,
       t1.fsubmitcount,
       t2.fsubmitcount as fmonthsubmitcount,
       case
           when DATEDIFF(DD, t1.createdate, GETDATE()) < 31 then '已合作' +
                                                                 convert(varchar(20), DATEDIFF(DD, t1.createdate, GETDATE())) +
                                                                 '天'
           when DATEDIFF(MM, t1.createdate, GETDATE()) < 12 then '已合作' +
                                                                 convert(varchar(20), DATEDIFF(MM, t1.createdate, GETDATE())) +
                                                                 '个月'
           else '已合作' + convert(varchar(20), DATEDIFF(YY, t1.createdate, GETDATE())) + '年' +
                convert(varchar(20),
                            DATEDIFF(MM, t1.createdate, GETDATE()) - DATEDIFF(YY, t1.createdate, GETDATE()) * 12) +
                '个月' end as fcooperattime
from tagents_workers t1
left join (
        select t1.tenterprise_userid, count(0) as fsubmitcount from tagents_workers t1
        inner join tagents_workersubmit t2 on t1.siteid = t2.siteid and t1.tagentsid = t2.tagentsid and t1.tenterprise_userid = t2.tenterprise_userid
        where t1.siteid =$siteid$ and t1.tagentsid=$tagentsid$ and $where$ and convert(varchar(7),t2.createdate,120)=convert(varchar(7),getdate(),120)
        group by t1.tenterprise_userid
) t2 on t1.tenterprise_userid = t2.tenterprise_userid
where t1.siteid =$siteid$ and t1.tagentsid=$tagentsid$ and $where$