Single item Flow

-- Method, billing date, document date, unit price, wholesale price, retail price, actual price, quantity, document number, inventory balance, flow unit, batch number

Set nocount on

Declare @ starttime char (19)
Declare @ stoptime char (19)
Declare @ nbbm decimal (18, 0)
-- Set @ date1 = getdate ()
Set @ starttime = '2017-08-21 00:00:00'
Set @ stoptime = '2017-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, jsdloud, 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 () -- number of initial warehouse receiving
Declare @ ini_cks decimal () -- initial sales count

-- 1. Obtain the total number of inventories in the first time period.
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
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
-- Process
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,. ypm,. GG,. CD,. 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. nbbm = B. nbbm order by B. rowid
Drop table # sun_web_pzlx

Set nocount off