业务员订单与开票记录表

set nocount on

declare @fbdate datetime,
@fedate datetime

set @fbdate='********'
set @fedate='########'

 

select
c.fname as f01name , b.ftotal
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
where a.fyear=year('********') or a.fyear=year('########')

 

select
c.fname as f02name , d.fname as fywname , d.fname as fbmname,
sum(b.fallstdamount)/10000 as fddtotal
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='')
group by a.fempid ,c.fname, d.fname
order by fddtotal

select

a.f03name , sum(a.fintotal) as fintotal
into #temp04
from (
SELECT
c.fname as f03name , sum(b.famountincludetax)/10000 as fintotal
from icsale a
left join icsaleentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.fempid
where a.fcancellation='0' and
(a.fdate>='********' or @fbdate='' ) and
( a.fdate<='########' or @fedate='') and a.ftrantype='80'
group by a.fempid ,c.fname

union all

select
c.fname as f03name , sum(b.fstdamount)/10000 as fintotal
from icsale a
left join icsaleentry b on b.finterid=a.finterid
left join t_emp c on c.fitemid=a.fempid
where a.fcancellation='0' and
(a.fdate>='********' or @fbdate='' ) and
( a.fdate<='########' or @fedate='') and a.ftrantype='86'
group by a.fempid ,c.fname ) a
group by a.f03name

select
b.f01name , b.ftotal , a.fbmname ,
a.f02name , a.fddtotal ,
c.f03name , c.fintotal
from #temp01 b
left join #temp02 a on a.f02name=b.f01name
left join #temp04 c on c.f03name=b.f01name

 

drop table #temp01
drop table #temp02
drop table #temp04