--方式,记帐日期,单据日期,单价,批发价,零售价,实际售价,数量,单据号,库存余量,流向单位,批号
SET NOCOUNT ON
declare @starttime char(19)
declare @stoptime char(19)
declare @nbbm decimal(18,0)
--set @date1=getdate()
set @starttime='2007-08-21 00:00:00'
set @stoptime='2007-08-23 23:59:59'
set @nbbm=10000290
select Identity(int,1,1) as rowid,nbbm,cfbj,djbh,jzrq,djrq,bzdj,pfj,lsj,isnull(jsdj,0) as jsdj,bzsl*bzl as bzsl,jsdjh,0 as kcl,dw,ph into #sun_web_pzlx from zy_yp1_sfmx where nbbm=@nbbm and jzrq>@starttime and jzrq<=@stoptime order by jzrq,ph,cfbj
declare @fs char(1)
declare @bzsl decimal(18,2)
declare @kcl decimal(18,2)
declare @rksl decimal(18,2)
declare @cksl decimal(18,2)
declare @rowid char(18)
declare @pri_kcl decimal(18,2)
declare @ini_rks decimal(18,2)--初始入库数
declare @ini_cks decimal(18,2)--初始销售数
--1.得到最先时间段的总库存数
set @ini_rks=(Select sum(bzsl) as zrzs from zy_yp1_sfmx where cfbj='T' AND nbbm=@nbbm and jzrq<@starttime)
set @ini_cks=(Select sum(bzsl) as ckzs from zy_yp1_sfmx where cfbj='F' AND nbbm=@nbbm and jzrq<@starttime)
set @kcl=@ini_rks-@ini_cks
--print @kcl
--select sum(bzl*bzsl*(case isnull(cfbj,'T') when 'T' then 1 else - 1 end)) from zy_yp1_sfmx where spmnm=@nbbm and jzrq<@starttime
DECLARE Get_pzlx CURSOR FOR
SELECT cfbj,bzsl,rowid
FROM #sun_web_pzlx order by rowid
OPEN Get_pzlx
FETCH NEXT FROM Get_pzlx INTO @fs, @bzsl,@rowid
SET @kcl=@kcl+(case when @fs='T' then 1 else -1 end )*@bzsl
UPDATE #sun_web_pzlx SET kcl=@kcl where rowid=@rowid
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Get_pzlx INTO @fs, @bzsl,@rowid
--处理
IF @@FETCH_STATUS = 0
BEGIN
SET @kcl=@kcl+(case when @fs='T' then 1 else -1 end )*@bzsl
UPDATE #sun_web_pzlx SET kcl=@kcl where rowid=@rowid
--print @@FETCH_STATUS
END
END
CLOSE Get_pzlx
DEALLOCATE Get_pzlx
select b.rowid,a.ypm,a.gg,a.cd,a.zxdw,b.kcl,b.bzsl,b.djbh,b.cfbj,b.jzrq,b.djrq,b.bzdj,b.pfj,b.lsj,b.jsdj,b.jsdjh,b.dw,b.ph from #sun_web_pzlx b,zy_sys2_ypzdk a where a.nbbm=b.nbbm order by b.rowid
drop table #sun_web_pzlx
SET NOCOUNT OFF