27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t_splsk TABLE (
id INT --ID,唯一
,plh CHAR(11) --结转号,唯一
,djbh CHAR(15) --单据编号
,dj_sn INT --单据序号
,sipd CHAR(11) --商品ID
,pihao CHAR(60) --批号
,pici CHAR(60) --批次
,rkshl INT --入库数量
,chkshl INT --出库数量
,jcshl INT --结存数量
)
--插入临时流水表
INSERT INTO @t_splsk ( id, plh, djbh, dj_sn, sipd, pihao, pici, rkshl, chkshl, jcshl )
SELECT 1,'Z0000298179','JHAGZZ00005980',9,'SPH00004013','130922','JHAGZZ00005980_9',13200,0,13200 UNION ALL
SELECT 2,'Z0000298191','XSAGZZ00024459',9,'SPH00004013','130922','',0,13200,0 UNION ALL
SELECT 3,'Z0000298540','JHAGZZ00005981',4,'SPH00004013','130922','JHAGZZ00005981_4',36000,0,36000 UNION ALL
SELECT 4,'Z0000298552','XSAGZZ00024489',4,'SPH00004013','130922','',0,36000,0 UNION ALL
SELECT 5,'Z0000305527','JHAZDA00006095',5,'SPH00004013','130922','JHAZDA00006095_5',1500,0,1500 UNION ALL
SELECT 6,'Z0000305530','JHAZDA00006095',4,'SPH00004013','130922','JHAZDA00006095_4',36300,0,37800 UNION ALL
SELECT 7,'Z0000305625','XSAZDA00025000',1,'SPH00004013','130922','',0,300,37500 UNION ALL
SELECT 8,'Z0000305645','XSAZDA00025003',37,'SPH00004013','130922','',0,9000,28500 UNION ALL
SELECT 9,'Z0000305647','XSAZDA00025004',1,'SPH00004013','130922','',0,1500,27000 UNION ALL
SELECT 10,'Z0000305804','XSAZDA00025028',1,'SPH00004013','130922','',0,600,26400 UNION ALL
SELECT 11,'Z0000305821','XSAZDA00025031',1,'SPH00004013','130922','',0,1500,24900 UNION ALL
SELECT 12,'Z0000305954','XSAZDA00025039',97,'SPH00004013','130922','',0,1500,23400 UNION ALL
SELECT 13,'Z0000306039','XSAZDA00025048',1,'SPH00004013','130922','',0,900,22500 UNION ALL
SELECT 14,'Z0000306244','XSAZDA00025054',33,'SPH00004013','130922','',0,1800,20700 UNION ALL
SELECT 15,'Z0000306428','XSAZDA00025076',2,'SPH00004013','130922','',0,300,20400 UNION ALL
SELECT 16,'Z0000306489','XSAZDA00025088',1,'SPH00004013','130922','',0,15000,5400 UNION ALL
SELECT 17,'Z0000306491','XSAZDA00025089',1,'SPH00004013','130922','',0,4200,1200 UNION ALL
SELECT 18,'Z0000306622','XSAZDA00025105',1,'SPH00004013','130922','',0,600,600 UNION ALL
SELECT 19,'Z0000314165','XSAZDA00025798',1,'SPH00004013','130922','',0,300,300 UNION ALL
SELECT 20,'Z0000317304','XSAZDA00026133',8,'SPH00004013','130922','',0,300,0
SELECT * FROM @t_splsk
--批次结存表
DECLARE @t_PICI TABLE (id INT,pici CHAR(60),rkshl INT,chkshl INT,jchsl INT)
INSERT INTO @t_PICI
SELECT ROW_NUMBER() OVER(ORDER BY plh ASC) AS id,pici,rkshl,0 AS chkshl,rkshl AS jcshl FROM @t_splsk WHERE pici<>'' ORDER BY plh
--批次循环
DECLARE @N INT,@t_N INT
SELECT @N = COUNT(*) FROM @t_PICI
SET @t_N = 1
WHILE @t_N <= @N
BEGIN
--获取批次
DECLARE @pici CHAR(60)
SELECT @pici = LTRIM(RTRIM(pici)) FROM @t_PICI WHERE id = @t_N
--流水循环
DECLARE @M INT,@t_M INT
SET @t_M = 1
SELECT @M = COUNT(*) FROM @t_splsk
WHILE @t_M <= @M
BEGIN
--获取批次结存数量
DECLARE @jcshl INT,@pc_chkshl INT
SET @jcshl = 0
SET @pc_chkshl = 0
SELECT @jcshl = jchsl,@pc_chkshl = chkshl FROM @t_PICI WHERE LTRIM(RTRIM(pici)) = LTRIM(RTRIM(@pici)) AND jchsl - chkshl >=0
--获取流水出,入库数量及结转号
DECLARE @plh CHAR(11),@rkshl INT,@chkshl INT,@bs CHAR(1)
SELECT @plh = plh,@rkshl = ISNULL(rkshl,0),@chkshl = ISNULL(chkshl,0)
,@bs = CASE WHEN LEFT(djbh,3)<>'JHA' AND @jcshl - @pc_chkshl + @rkshl - @chkshl >= 0 AND pici ='' THEN 'Y' ELSE 'N' END
FROM @t_splsk WHERE id = @t_M
IF @bs ='Y'
BEGIN
IF @jcshl + @rkshl - @chkshl >=0
BEGIN
UPDATE @t_PICI
SET chkshl = chkshl + ISNULL(@rkshl,0) + ISNULL(@chkshl,0)
WHERE LTRIM(RTRIM(pici)) = LTRIM(RTRIM(@pici))
UPDATE @t_splsk
SET pici = LTRIM(RTRIM(@pici))
WHERE plh = @plh
END
END
SET @t_M = @t_M + 1
END
SET @t_N = @t_N + 1
END
--展现
SELECT * FROM @t_splsk ORDER BY id
DECLARE @t_splsk TABLE (
id INT primary key --要加主键,否则不能用游标更新
...
)
INSERT INTO @t_splsk ...
DECLARE @t_pikc TABLE ( -- 批次库存
id INT --@t_splsk.id,判断先后的依据
,pici CHAR(60) --批次
,jcshl INT --结存数量
)
DECLARE @id int
DECLARE @pici char(60)
DECLARE @rkshl int
DECLARE @chkshl int
DECLARE cur_splsk CURSOR
FOR
SELECT id, pici, rkshl, chkshl
FROM @t_splsk
ORDER BY id
FOR UPDATE
OPEN cur_splsk
FETCH NEXT FROM cur_splsk
INTO @id, @pici, @rkshl, @chkshl
WHILE @@FETCH_STATUS = 0
BEGIN
IF @pici <> ''
BEGIN
INSERT INTO @t_pikc VALUES(@id, @pici, @rkshl)
END
ELSE
BEGIN
DECLARE @kc_id int
DECLARE @kc_pici char(60)
DECLARE @kc_jcshl int
SELECT TOP 1
@kc_id = id,
@kc_pici = pici,
@kc_jcshl = jcshl
FROM @t_pikc
WHERE jcshl >= @chkshl
ORDER BY id
UPDATE @t_splsk
SET pici = @kc_pici
WHERE CURRENT OF cur_splsk
UPDATE @t_pikc
SET jcshl = jcshl - @chkshl
WHERE id = @kc_id
END
FETCH NEXT FROM cur_splsk
INTO @id, @pici, @rkshl, @chkshl
END
CLOSE cur_splsk
DEALLOCATE cur_splsk
SELECT * FROM @t_splsk
SELECT * FROM @t_pikc
id plh djbh dj_sn sipd pihao pici rkshl chkshl jcshl
----------- ----------- --------------- ----------- ----------- -------- ------------------ ----------- ----------- -----------
1 Z0000298179 JHAGZZ00005980 9 SPH00004013 130922 JHAGZZ00005980_9 13200 0 13200
2 Z0000298191 XSAGZZ00024459 9 SPH00004013 130922 JHAGZZ00005980_9 0 13200 0
3 Z0000298540 JHAGZZ00005981 4 SPH00004013 130922 JHAGZZ00005981_4 36000 0 36000
4 Z0000298552 XSAGZZ00024489 4 SPH00004013 130922 JHAGZZ00005981_4 0 36000 0
5 Z0000305527 JHAZDA00006095 5 SPH00004013 130922 JHAZDA00006095_5 1500 0 1500
6 Z0000305530 JHAZDA00006095 4 SPH00004013 130922 JHAZDA00006095_4 36300 0 37800
7 Z0000305625 XSAZDA00025000 1 SPH00004013 130922 JHAZDA00006095_5 0 300 37500
8 Z0000305645 XSAZDA00025003 37 SPH00004013 130922 JHAZDA00006095_4 0 9000 28500
9 Z0000305647 XSAZDA00025004 1 SPH00004013 130922 JHAZDA00006095_4 0 1500 27000
10 Z0000305804 XSAZDA00025028 1 SPH00004013 130922 JHAZDA00006095_5 0 600 26400
11 Z0000305821 XSAZDA00025031 1 SPH00004013 130922 JHAZDA00006095_4 0 1500 24900
12 Z0000305954 XSAZDA00025039 97 SPH00004013 130922 JHAZDA00006095_4 0 1500 23400
13 Z0000306039 XSAZDA00025048 1 SPH00004013 130922 JHAZDA00006095_4 0 900 22500
14 Z0000306244 XSAZDA00025054 33 SPH00004013 130922 JHAZDA00006095_4 0 1800 20700
15 Z0000306428 XSAZDA00025076 2 SPH00004013 130922 JHAZDA00006095_5 0 300 20400
16 Z0000306489 XSAZDA00025088 1 SPH00004013 130922 JHAZDA00006095_4 0 15000 5400
17 Z0000306491 XSAZDA00025089 1 SPH00004013 130922 JHAZDA00006095_4 0 4200 1200
18 Z0000306622 XSAZDA00025105 1 SPH00004013 130922 JHAZDA00006095_4 0 600 600
19 Z0000314165 XSAZDA00025798 1 SPH00004013 130922 JHAZDA00006095_5 0 300 300
20 Z0000317304 XSAZDA00026133 8 SPH00004013 130922 JHAZDA00006095_4 0 300 0
id pici jcshl
----------- ------------------ -----------
1 JHAGZZ00005980_9 0
3 JHAGZZ00005981_4 0
5 JHAZDA00006095_5 0
6 JHAZDA00006095_4 0