select t.fcustaction, count(0) fcount
from (select distinct t1.tcustomerid, t2.fvalue fcustaction, t2.sequence
      from tcustomer_reception t1
               inner join ttypedetail t2 on t1.siteid = t2.siteid and t2.ftype = '销售动作' and
                                            t1.fcustaction like '%' + t2.fvalue + '%'
               inner join tagents t3 on t1.siteid = t3.siteid and t1.tagentsid = t3.tagentsid
      where t1.siteid =$siteid$
        and convert (varchar (7)
          , t1.fdate
          , 120)>=$fbegmonth$
        and convert (varchar (7)
          , t1.fdate
          , 120)<=$fendmonth$
      union
select distinct t1.tcustomerid, t2.fvalue fcustaction, t2.sequence
from tcustomer_reception t1
         inner join ttypedetail t2 on t1.siteid = t2.siteid and t2.ftype = '销售动作'
         inner join tagents t3 on t1.siteid = t3.siteid and t1.tagentsid = t3.tagentsid
where t1.fcustaction = '成交' and t2.fisused=1
  and t1.siteid =$siteid$
  and convert (varchar (7)
    , t1.fdate
    , 120)>=$fbegmonth$
  and convert (varchar (7)
    , t1.fdate
    , 120)<=$fendmonth$
union
select distinct t1.tcustomerid, t1.fcustaction fcustaction, 9999
from tcustomer_reception t1
         inner join tagents t2 on t1.siteid = t2.siteid and t1.tagentsid = t2.tagentsid
where t1.fcustaction = '成交'
  and t1.siteid =$siteid$
  and convert (varchar (7)
    , t1.fdate
    , 120)>=$fbegmonth$
  and convert (varchar (7)
    , t1.fdate
    , 120)<=$fendmonth$) t
group by t.fcustaction, t.sequence
order by t.sequence


