以#,##开头的是临时表CREATE PROCEDURE y_kpyxs
@start_rq char(10),
@erq char(10)AS--汇总销售出库单
select a.username,sum(b.hsje) as xshje,sum(b.ml) as ml into #table1
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.xgdjbh and b.djbh like 'XSA%'
group by a.username--汇总销售退回单
select a.username,sum(b.hsje) as xshje,sum(b.ml) as ml into #table2
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.djbh and b.djbh like 'XSC%'
group by a.username
--汇总销售退补价单
select a.username,sum(b.hsje) as xshje,sum(b.hsje) as ml into #table3
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.xgdjbh and b.djbh like 'XSB%'
group by a.username--筛选操作员名单
select dzyname into #table4 from zhiydoc(nolock) where beactive='是' and is_czy='是'--汇总计算销售额和毛利
select table4.dzyname,(isnull(#table1.xshje,0)+isnull(#table2.xshje,0)+isnull(#table3.xshje,0)) as xshje,
(isnull(#table1.ml,0)+isnull(#table2.ml,0)+isnull(#table3.ml,0)) as ml into #table5
from #table4
left join #table1 on #table1.username=#table4.dzyname
left join #table2 on #table2.username=#table4.dzyname
left join #table3 on #table3.username=#table4.dzyname
where (isnull(#table1.xshje,0)+isnull(#table2.xshje,0)+isnull(#table3.xshje,0))<>0--综合查询结果
select dzyname,xshje,ml,ml/xshje*100 as mll from #table5
--删除临时表
drop table #table1
drop table #table2
drop table #table3
drop table #table4
drop table #table5GO
@start_rq char(10),
@erq char(10)AS--汇总销售出库单
select a.username,sum(b.hsje) as xshje,sum(b.ml) as ml into #table1
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.xgdjbh and b.djbh like 'XSA%'
group by a.username--汇总销售退回单
select a.username,sum(b.hsje) as xshje,sum(b.ml) as ml into #table2
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.djbh and b.djbh like 'XSC%'
group by a.username
--汇总销售退补价单
select a.username,sum(b.hsje) as xshje,sum(b.hsje) as ml into #table3
from jxdjhz a(nolock),ywmxk b(nolock)
where a.rq>=@start_rq and a.rq<=@erq and a.djbh=b.xgdjbh and b.djbh like 'XSB%'
group by a.username--筛选操作员名单
select dzyname into #table4 from zhiydoc(nolock) where beactive='是' and is_czy='是'--汇总计算销售额和毛利
select table4.dzyname,(isnull(#table1.xshje,0)+isnull(#table2.xshje,0)+isnull(#table3.xshje,0)) as xshje,
(isnull(#table1.ml,0)+isnull(#table2.ml,0)+isnull(#table3.ml,0)) as ml into #table5
from #table4
left join #table1 on #table1.username=#table4.dzyname
left join #table2 on #table2.username=#table4.dzyname
left join #table3 on #table3.username=#table4.dzyname
where (isnull(#table1.xshje,0)+isnull(#table2.xshje,0)+isnull(#table3.xshje,0))<>0--综合查询结果
select dzyname,xshje,ml,ml/xshje*100 as mll from #table5
--删除临时表
drop table #table1
drop table #table2
drop table #table3
drop table #table4
drop table #table5GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货