部门品种客户销售情况分析去物流

select c.spid,c.djbh,c.rq,a.spbh,a.spmch,a.shpgg,a.dw,a.jlgg,a.shengccj,a.shpchd,b.danwbh,b.dwmch,b.quyufl,b.kehulb,c.pihao,e.sxrq,c.pici,c.shl,c.hsjj,e.pckhdj,c.hshj,c.hsjj*c.shl as '含税成本',c.hsje,c.ml,c.ml2 as 业务毛利,d.bm,d.thfs,d.kpman,d.ywy,b.ywy as '单位对应业务员',f.dwmch as 供应商,e.fukfs as '来货付款方式',d.fukfs as '销售付款方式',f.cglx,f.type_cg,a.spcglx,a.cgy,e.ywlxr

into #1 from spkfk a

join pf_ckmx c on a.spid=c.spid

join pf_ckhz d on c.djbh=d.djbh and d.bm='新品部'

join mchk b on b.dwbh=d.dwbh and b.dwbh not in (select dwbh from mchk where dwmch='山西振东医药物流有限公司' and isxs='是')

left join sphwph e on c.pici=e.pici and c.hw=e.hw

join mchk f on f.dwbh=e.dwbh 

where  (c.rq>='2016-03-01' and c.rq<='2016-03-31')

select spid,dj,hshj,dwmch 

into #2 from (select a.spid,a.dj,c.dwmch,a.hshj,row_number() over (PARTITION BY a.spid order by a.rq desc) row from jh_rkmx a,jh_rkhz b,mchk c where b.dwbh=c.dwbh and a.djbh like 'jha%' and a.djbh=b.djbh and b.rq<='2016-03-31') b

where row=1

---品种

select a.spbh,a.spmch,a.shpgg,a.dw,a.jlgg,a.shengccj,b.dj,b.hshj,b.dwmch,sum(a.shl) shl,convert(decimal(10,2),sum(a.hsje)/sum(a.shl)) 平均单价,sum(a.hsje) hsje,sum(a.ml) ml,sum(a.业务毛利) 业务毛利,convert(decimal(10,4),sum(a.ml)/sum(a.hsje)) 财务毛利率,convert(decimal(10,4),sum(a.业务毛利)/sum(a.hsje)) 业务毛利率,c.kcshl,c.kcje

from #1 a,#2 b,spkfjc c

where a.spid=b.spid and a.spid=c.spid

group by a.spbh,a.spmch,a.shpgg,a.dw,a.jlgg,a.shengccj,b.dj,b.hshj,b.dwmch,c.kcshl,c.kcje

having sum(a.shl)<>0 and sum(a.hsje)<>0

---客户

select a.danwbh,a.dwmch,a.kehulb,sum(a.hsje) hsje,sum(a.ml) ml,sum(a.业务毛利) 业务毛利,convert(decimal(10,4),sum(a.ml)/sum(a.hsje)) 财务毛利率,convert(decimal(10,4),sum(a.业务毛利)/sum(a.hsje)) 业务毛利率

from #1 a

group by a.danwbh,a.dwmch,a.kehulb

having sum(a.hsje)<>0