部門品種客戶銷售情況分析去物流

部門品種客戶銷售情況分析去物流

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