K3业务员指标分析表
set nocount on
declare @fbdate datetime,
@fedate datetime
set @fbdate='********'
set @fedate='########'
----取指标值
select
c.fname as f01name , sum(b.ftotal) as ftotal ,d.fname as fbmname
into #temp01
from SOP_Bizrepsalesplan a
left join SOP_Bizrepsalesplanentry b on b.FID=a.FID
left join t_emp c on c.fitemid=b.fbizrepid
left join t_department d on d.fitemid=c.fdepartmentid
where b.ftotal>'0' AND ( a.fyear=year('********') OR a.fyear=year('########'))
GROUP BY c.fname ,d.fname
----取订单值:
select c.fname as f01name , d.fname as fbmname , b.fallstdamount as fddamout , '0'as foldddamout
into #temp02
from seorder a
left join seorderentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.fempid
left join t_department d on d.fitemid=c.fdepartmentid
where a.fcancellation=0 and
(a.fdate>='********' or @fbdate='' ) and
( a.fdate<='########' or @fedate='')
----取同期订单值
select c.fname as f01name , d.fname as fbmname ,'0' as fddamout , b.fallstdamount as foldddamout
into #temp03
from seorder a
left join seorderentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.fempid
left join t_department d on d.fitemid=c.fdepartmentid
where a.fcancellation=0 and
(a.fdate>=dateadd(year,-1,'********') or @fbdate='' ) and
( a.fdate<=dateadd(year,-1,'########') or @fedate='')
----订单值合并
select a.f01name as f02name , a.fbmname , a.fddamout , a.foldddamout
into #temp06
from (
select f01name , fbmname ,sum(fddamout)/10000 as fddamout , foldddamout
from #temp02
group by f01name , fbmname ,foldddamout
union
select f01name , fbmname , fddamout , sum(foldddamout)/10000 as foldddamout
from #temp03
group by f01name , fbmname ,fddamout) a
----订单值合并同类
select a.f02name , a.fbmname , sum(a.fddamout) as fddamout , sum(a.foldddamout) as foldddamout
into #temp08
from #temp06 a
group by a.f02name , a.fbmname
----增值发票取值
select c.fname as f01name , d.fname as fbmname , b.famountincludetax as finamout
into #temp04
from icsale a
left join icsaleentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.FEmpID
left join t_department d on d.fitemid=a.FDeptID
where a.fcancellation='0' and a.ftrantype='80' and
(a.fdate>='********' or @fbdate='' ) and
( a.fdate<='########' or @fedate='')
select c.fname as f01name , d.fname as fbmname , b.fstdamount as finamout
into #temp05
from icsale a
left join icsaleentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.fempid
left join t_department d on d.fitemid=a.FDeptID
where a.fcancellation='0' and a.ftrantype='86' and
(a.fdate>='********' or @fbdate='' ) and
( a.fdate<='########' or @fedate='')
----增值发票合计值
select a.f01name as f03name ,a.fbmname , finamout
into #temp07
from (
select f01name , fbmname , sum(finamout)/10000 AS finamout
from #temp04
group by f01name , fbmname
union all
select f01name , fbmname , sum(finamout)/10000 AS finamout
from #temp05
group by f01name , fbmname)a
----增值发票合并值
select a.f03name , a.fbmname , sum(a.finamout) as finamout
into #temp09
from #temp07 a
group by a.f03name , a.fbmname
----报表合并
select
b.f01name , b.ftotal , b.fbmname ,
a.f02name , a.fddamout, a.foldddamout,
c.f03name , c.finamout
from #temp01 b
left join #temp08 a on a.f02name=b.f01name and a.fbmname=b.fbmname
left join #temp09 c on c.f03name=b.f01name and c.fbmname=b.fbmname
drop table #temp01
drop table #temp02
drop table #temp03
drop table #temp04
drop table #temp05
drop table #temp06
drop table #temp07
drop table #temp08
drop table #temp09