货位 检索方案zl_select_zr_120hw

120
编号 检索方案 cskc
select a.spid,a.spbh,a.spmch,a.shpgg,a.shpchd,a.dw,a.jlgg,a.jixing,a.shengccj,a.pizhwh,a.zjm,b.shl,
a.shlv,b.hshj/(1+a.shlv/100) as chbdj,b.hshj/(1+a.shlv/100) as jj,hshj as hshjj,a.lshj,hshj as hesj,
isnull(b.pihao,'') as pihao,isnull(b.sxrq,'') as sxrq,
b.hw,b.hwbh,b.huowname
from spkfk a(nolock),太刺激了 b(nolock)
where a.spbh=b.spbh and a.beactive='是'

select a.spbh,a.spmch,a.shpchd,a.shlv ,a.dw,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
b.shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c
where b.hw='HWI00000027' and b.spid=c.spid and b.hw=c.hw
and a.spid=b.spid

1,27 和 28 ，负数退永康
select a.spbh,a.spmch,a.shpchd,a.shlv ,d.huowname,a.dw,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
-b.shl as shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c,huoweizl d
where b.hw='HWI00000028' and b.spid=c.spid and b.hw=c.hw and b.hw=d.hw
and a.spid=b.spid and b.shl<>0

2，导中药库存
select a.spbh,a.spmch,a.shpchd,a.shlv ,d.huowname,a.dw,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
-b.shl as shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c,huoweizl d
where b.hw='HWI00000028' and b.spid=c.spid and b.hw=c.hw and b.hw=d.hw and a.leibie='中药'
and a.spid=b.spid and b.shl<>0

3，导指定药品

select a.spbh,a.spmch,a.shpchd,a.shlv ,d.huowname,a.dw,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
-b.shl as shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c,huoweizl d
where b.hw='HWI00000028' and b.spid=c.spid and b.hw=c.hw and b.hw=d.hw and a.spbh in ('30000','18547')
and a.spid=b.spid and b.shl<>0

4，最终调库正
select a.spbh,a.spmch,a.shpchd,a.shlv,a.dw,'HWI00000124' as hw,'鑫浩药店' as huowname,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
b.shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c,huoweizl d
where b.hw='HWI00000028' and b.spid=c.spid and b.hw=c.hw and b.hw=d.hw and a.leibie<>'中药' and d.beactive='是'
and a.spid=b.spid and b.shl<>0

5，永康减库存
select a.spbh,a.spmch,a.shpchd,a.shlv,a.dw,'HWI00000028' as hw,'永康药店' as huowname,a.shpgg,a.lshj,a.jlgg,c.chbdj as jj,
c.chbdj*(a.shlv/100+1) as hshjj ,
-b.shl as shl,b.pihao,b.baozhiqi,b.sxrq,a.spid from spkfk a,sphwph b,hwsp c,huoweizl d
where b.hw='HWI00000028' and b.spid=c.spid and b.hw=c.hw and b.hw=d.hw and d.beactive='是'
and a.spid=b.spid and b.shl<>0