select tt.siteid,
       tt.fsalername,
       tt.fsaleruserid,
       tt.fagentnum,
       tt.fagentshortname,
       tt.fprovince,tt.fcity,tt.fcounty,
       tt.fdays,
       case when tt.fdays >= 1 then 1 else 0 end oneday,
       case when tt.fdays >= 3 then 1 else 0 end threeday,
       case when tt.fdays >= 7 then 1 else 0 end sevenday
from (
         select t.siteid,
                fsaleruserid,
                t.fagentnum,
                t.fagentshortname,t.fprovince,t.fcity,t.fcounty,t.fsalername,
                DATEDIFF(day, convert(varchar(10), max(t.createdate), 120), convert(varchar(10), GETDATE(), 120)) fdays
         from (
                  select t1.siteid,fsaleruserid,t3.fname as fsalername, t1.fagentnum, t1.fagentshortname,t1.fprovince,t1.fcity,t1.fcounty, MAX(isnull(t2.createdate, '1999-01-01')) createdate
                  from tagents t1
                           left join tcustomer_reception t2 on t1.siteid = t2.siteid and t1.tagentsid = t2.tagentsid
                           left join tenterprise_users t3 on t1.siteid=t3.siteid and t1.fsaleruserid=t3.tenterprise_userid
                  where t1.siteid=$siteid$ and t1.fsaleruserid in $fsaleruserid$
                  group by t1.siteid, t1.fagentnum, t1.fagentshortname,t1.fsaleruserid,t1.fprovince,t1.fcity,t1.fcounty,t3.fname
                  union all
                  select t1.siteid,fsaleruserid,t3.fname as fsalername, t1.fagentnum, t1.fagentshortname,t1.fprovince,t1.fcity,t1.fcounty, MAX(isnull(t2.createdate, '1999-01-01')) createdate
                  from tagents t1
                           left join tsaleevent t2 on t1.siteid = t2.siteid and t1.tagentsid = t2.tagentsid
                           left join tenterprise_users t3 on t1.siteid=t3.siteid and t1.fsaleruserid=t3.tenterprise_userid
                  where t1.siteid=$siteid$ and t1.fsaleruserid in $fsaleruserid$
                  group by t1.siteid, t1.fagentnum, t1.fagentshortname,t1.fsaleruserid,t1.fprovince,t1.fcity,t1.fcounty,t3.fname
                  union all
select t1.siteid,fsaleruserid,t3.fname as fsalername, t1.fagentnum, t1.fagentshortname,t1.fprovince,t1.fcity,t1.fcounty, MAX(isnull(t2.createdate, '1999-01-01')) createdate
from tagents t1
         left join tagents_cost t2 on t1.siteid = t2.siteid and t1.tagentsid = t2.tagentsid
         left join tenterprise_users t3 on t1.siteid=t3.siteid and t1.fsaleruserid=t3.tenterprise_userid
where t1.siteid=$siteid$ and t1.fsaleruserid in $fsaleruserid$
group by t1.siteid, t1.fagentnum, t1.fagentshortname,t1.fsaleruserid,t1.fprovince,t1.fcity,t1.fcounty,t3.fname
    ) t
group by t.siteid, t.fagentnum, t.fagentshortname,t.fsaleruserid,t.fprovince,t.fcity,t.fcounty,t.fsalername
    ) tt
