高难度收发存函数,希望能做成函数
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE (STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80) ,
UN VARCHAR(20), SQT FLOAT , IQT FLOAT , OQT FLOAT ,CQT FLOAT) I_INSTORE 入库表 (注意是同一天同一货品的 单据里面的BILLID是不相同的)
BILLID STORE BD BC GOODS UN QT
BILLID 仓库 日期 单号 货品 单位 数量
1001 半成品仓 2009-07-01 090701001 16081D 个 300
1002 半成品仓 2009-07-02 090702003 16081D 个 50
1003 半成品仓 2009-07-18 090718003 16081D 个 90
1004 半成品仓 2009-07-18 090718004 16081D 个 33 1009 半成品仓 2009-07-01 090701002 16082D 个 50 1010 半成品仓 2009-07-15 090715007 16083D 个 80 1020 成品仓 2009-07-02 090702007 16081D 个 50
1022 成品仓 2009-07-03 090703008 16081D 个 77 I_OUTSTORE 出库表 (注意是同一天同一货品的 单据里面的BILLID是不相同的)
BILLID STORE BD BC GOODS UN QT
BILLID 仓库 日期 单号 货品 单位 数量
3001 半成品仓 2009-07-05 090705001 16081D 个 30 3002 半成品仓 2009-07-18 090718002 16082D 个 20 (注意是同一天同一货品的)
3003 半成品仓 2009-07-18 090718006 16082D 个 25 (注意是同一天同一货品的)3004 半成品仓 2009-07-23 090723007 16083D 个 35 3006 成品仓 2009-07-28 090728026 16081D 个 20
要求计算结果如下显示 (计算每天的期初和结存) 最关键的是每天的结存
本日结存作为明天的期初,如此累推. 例如查询日期 开始日期:2009-07-03 结束日期:2009-07-28 (注意不同的时间段期初是不一样的)
STORE BD BC GOODS UN SQT IQT OQT CQT
仓库 日期 单号 货品 单位 期初 收入 发出 结存
半成品仓 2009-07-03 期初 16081D 个 350 0 0 350
半成品仓 2009-07-05 090705001 16081D 个 350 0 30 320
半成品仓 2009-07-18 090718003 16081D 个 320 90 0 410 (同一天的)
半成品仓 2009-07-18 090718004 16081D 个 410 33 0 443
半成品仓 2009-07-28 结存 16081D 个 350 123 30 443半成品仓 2009-07-03 期初 16082D 个 50 0 0 50
半成品仓 2009-07-18 090718002 16082D 个 50 0 20 30 (同一天的)
半成品仓 2009-07-18 090718006 16082D 个 30 0 25 5
半成品仓 2009-07-28 结存 16082D 个 50 0 45 5半成品仓 2009-07-03 期初 16083D 个 0 0 0 0
半成品仓 2009-07-15 090715007 16083D 个 0 80 0 80
半成品仓 2009-07-23 090723007 16083D 个 80 0 35 45
半成品仓 2009-07-28 结存 16083D 个 0 80 35 45成品仓 2009-07-03 期初 16081D 个 50 0 0 50
成品仓 2009-07-03 090703008 16081D 个 50 77 0 125
成品仓 2009-07-28 090728026 16081D 个 125 0 20 105
成品仓 2009-07-28 结存 16081D 个 50 77 20 105
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE (STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80) ,
UN VARCHAR(20), SQT FLOAT , IQT FLOAT , OQT FLOAT ,CQT FLOAT) I_INSTORE 入库表 (注意是同一天同一货品的 单据里面的BILLID是不相同的)
BILLID STORE BD BC GOODS UN QT
BILLID 仓库 日期 单号 货品 单位 数量
1001 半成品仓 2009-07-01 090701001 16081D 个 300
1002 半成品仓 2009-07-02 090702003 16081D 个 50
1003 半成品仓 2009-07-18 090718003 16081D 个 90
1004 半成品仓 2009-07-18 090718004 16081D 个 33 1009 半成品仓 2009-07-01 090701002 16082D 个 50 1010 半成品仓 2009-07-15 090715007 16083D 个 80 1020 成品仓 2009-07-02 090702007 16081D 个 50
1022 成品仓 2009-07-03 090703008 16081D 个 77 I_OUTSTORE 出库表 (注意是同一天同一货品的 单据里面的BILLID是不相同的)
BILLID STORE BD BC GOODS UN QT
BILLID 仓库 日期 单号 货品 单位 数量
3001 半成品仓 2009-07-05 090705001 16081D 个 30 3002 半成品仓 2009-07-18 090718002 16082D 个 20 (注意是同一天同一货品的)
3003 半成品仓 2009-07-18 090718006 16082D 个 25 (注意是同一天同一货品的)3004 半成品仓 2009-07-23 090723007 16083D 个 35 3006 成品仓 2009-07-28 090728026 16081D 个 20
要求计算结果如下显示 (计算每天的期初和结存) 最关键的是每天的结存
本日结存作为明天的期初,如此累推. 例如查询日期 开始日期:2009-07-03 结束日期:2009-07-28 (注意不同的时间段期初是不一样的)
STORE BD BC GOODS UN SQT IQT OQT CQT
仓库 日期 单号 货品 单位 期初 收入 发出 结存
半成品仓 2009-07-03 期初 16081D 个 350 0 0 350
半成品仓 2009-07-05 090705001 16081D 个 350 0 30 320
半成品仓 2009-07-18 090718003 16081D 个 320 90 0 410 (同一天的)
半成品仓 2009-07-18 090718004 16081D 个 410 33 0 443
半成品仓 2009-07-28 结存 16081D 个 350 123 30 443半成品仓 2009-07-03 期初 16082D 个 50 0 0 50
半成品仓 2009-07-18 090718002 16082D 个 50 0 20 30 (同一天的)
半成品仓 2009-07-18 090718006 16082D 个 30 0 25 5
半成品仓 2009-07-28 结存 16082D 个 50 0 45 5半成品仓 2009-07-03 期初 16083D 个 0 0 0 0
半成品仓 2009-07-15 090715007 16083D 个 0 80 0 80
半成品仓 2009-07-23 090723007 16083D 个 80 0 35 45
半成品仓 2009-07-28 结存 16083D 个 0 80 35 45成品仓 2009-07-03 期初 16081D 个 50 0 0 50
成品仓 2009-07-03 090703008 16081D 个 50 77 0 125
成品仓 2009-07-28 090728026 16081D 个 125 0 20 105
成品仓 2009-07-28 结存 16081D 个 50 77 20 105
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-28 20:45:24
-------------------------------------
--> 生成测试数据: [I_INSTORE]
IF OBJECT_ID('[I_INSTORE]') IS NOT NULL DROP TABLE [I_INSTORE]
CREATE TABLE [I_INSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_INSTORE]
SELECT 1001,'半成品仓','2009-07-01','090701001','16081D','个',300 UNION ALL
SELECT 1002,'半成品仓','2009-07-02','090702003','16081D','个',50 UNION ALL
SELECT 1003,'半成品仓','2009-07-18','090718003','16081D','个',90 UNION ALL
SELECT 1004,'半成品仓','2009-07-18','090718004','16081D','个',33 UNION ALL
SELECT 1009,'半成品仓','2009-07-01','090701002','16082D','个',50 UNION ALL
SELECT 1010,'半成品仓','2009-07-15','090715007','16083D','个',80 UNION ALL
SELECT 1020,'成品仓','2009-07-02','090702007','16081D','个',50 UNION ALL
SELECT 1022,'成品仓','2009-07-03','090703008','16081D','个',77
--> 生成测试数据: [I_OUTSTORE]
IF OBJECT_ID('[I_OUTSTORE]') IS NOT NULL DROP TABLE [I_OUTSTORE]
CREATE TABLE [I_OUTSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_OUTSTORE]
SELECT 3001,'半成品仓','2009-07-05','090705001','16081D','个',30 UNION ALL
SELECT 3002,'半成品仓','2009-07-18','090718002','16082D','个',20 UNION ALL
SELECT 3003,'半成品仓','2009-07-18','090718006','16082D','个',25 UNION ALL
SELECT 3004,'半成品仓','2009-07-23','090723007','16083D','个',35 UNION ALL
SELECT 3006,'成品仓','2009-07-28','090728026','16081D','个',20--SQL查询如下:GO
--2009-07-03 结束日期:2009-07-28CREATE PROC dbo.R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME
)
AS
DECLARE @p_tb TABLE(STORE VARCHAR(80), GOODS VARCHAR(80),UN VARCHAR(20),QT NUMERIC(10,2));
--计算期初数据
INSERT @p_tb
SELECT A.STORE,A.GOODS,A.UN,ISNULL(B.QT,0) AS QT
FROM (
SELECT STORE,GOODS,UN
FROM [I_INSTORE] GROUP BY STORE,GOODS,UN
) AS A
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_INSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS B
ON A. STORE=B.STORE AND A.GOODS=B.GOODS; --计算明细数据
DECLARE @tb TABLE(STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80),
UN VARCHAR(20), SQT NUMERIC(10,2), IQT NUMERIC(10,2) ,
OQT NUMERIC(10,2) ,CQT NUMERIC(10,2)); INSERT @tb
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT ISNULL(A.STORE,B.STORE) AS STORE,
ISNULL(A.BD,B.BD) AS BD,
ISNULL(A.BC,B.BC) AS BC,
ISNULL(A.GOODS,B.GOODS) AS GOODS,
ISNULL(A.UN,B.UN) AS UN,
0 AS SQT,
ISNULL(A.QT,0) AS IQT,
ISNULL(B.QT,0) AS OQT,
0 AS CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,QT,
rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
FROM [I_INSTORE]
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS A
FULL JOIN (
SELECT STORE,BD,BC,GOODS,UN,QT,
rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
FROM [I_OUTSTORE]
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS B
ON A.GOODS=B.GOODS AND A.BD=B.BD AND A.STORE=B.STORE AND A.rowid=B.rowid
) AS T
--连接上期初数据和结存数据
UNION ALL
SELECT STORE,@FROMDATE,'期初',GOODS,UN,QT,0,0,QT FROM @p_tb
UNION ALL
SELECT STORE,@TODATE,'结存',GOODS,UN,QT,0,0,QT FROM @p_tb
) AS T
ORDER BY GOODS,STORE,BD; DECLARE @STORE VARCHAR(80),@GOODS VARCHAR(80);
DECLARE @CQT NUMERIC(10,2),@IQT NUMERIC(10,2),@OQT NUMERIC(10,2);
DECLARE @p_CQT NUMERIC(10,2); SELECT @CQT=0,@IQT=0,@OQT=0,@p_CQT=0; --更新库存
UPDATE @tb SET
@IQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN IQT + @IQT ELSE 0 END,
@OQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN OQT + @OQT ELSE 0 END ,
IQT = CASE WHEN BC = '结存' THEN @IQT ELSE IQT END,
OQT = CASE WHEN BC = '结存' THEN @OQT ELSE OQT END,
@p_CQT = @CQT,
@CQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS
THEN @CQT + IQT -OQT ELSE CQT END,
SQT = CASE WHEN BC IN('期初','结存') THEN SQT ELSE @p_CQT END,
CQT = @CQT,
@STORE = STORE,
@GOODS = GOODS; --查看数据
SELECT * FROM @tb;
GO EXEC dbo.R_I_INOUTSTOR '2009-07-03','2009-07-28'GO
--删除测试
DROP TABLE [I_INSTORE],[I_OUTSTORE];
DROP PROC dbo.R_I_INOUTSTOR;/*
STORE BD BC GOODS UN SQT IQT OQT CQT
---------- ----------------------- -------------------- -------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
半成品仓 2009-07-03 00:00:00.000 期初 16081D 个 350.00 0.00 0.00 350.00
半成品仓 2009-07-05 00:00:00.000 090705001 16081D 个 350.00 0.00 30.00 320.00
半成品仓 2009-07-18 00:00:00.000 090718004 16081D 个 320.00 33.00 0.00 353.00
半成品仓 2009-07-18 00:00:00.000 090718003 16081D 个 353.00 90.00 0.00 443.00
半成品仓 2009-07-28 00:00:00.000 结存 16081D 个 350.00 123.00 30.00 443.00
成品仓 2009-07-03 00:00:00.000 期初 16081D 个 50.00 0.00 0.00 50.00
成品仓 2009-07-03 00:00:00.000 090703008 16081D 个 50.00 77.00 0.00 127.00
成品仓 2009-07-28 00:00:00.000 090728026 16081D 个 127.00 0.00 20.00 107.00
成品仓 2009-07-28 00:00:00.000 结存 16081D 个 50.00 77.00 20.00 107.00
半成品仓 2009-07-03 00:00:00.000 期初 16082D 个 50.00 0.00 0.00 50.00
半成品仓 2009-07-18 00:00:00.000 090718002 16082D 个 50.00 0.00 20.00 30.00
半成品仓 2009-07-18 00:00:00.000 090718006 16082D 个 30.00 0.00 25.00 5.00
半成品仓 2009-07-28 00:00:00.000 结存 16082D 个 50.00 0.00 45.00 5.00
半成品仓 2009-07-03 00:00:00.000 期初 16083D 个 0.00 0.00 0.00 0.00
半成品仓 2009-07-15 00:00:00.000 090715007 16083D 个 0.00 80.00 0.00 80.00
半成品仓 2009-07-23 00:00:00.000 090723007 16083D 个 80.00 0.00 35.00 45.00
半成品仓 2009-07-28 00:00:00.000 结存 16083D 个 0.00 80.00 35.00 45.00(17 行受影响)*/
经本人测试新增存储过程时有如下提示:服务器: 消息 195,级别 15,状态 10,过程 R_I_INOUTSTOR,行 43
'ROW_NUMBER' 不是可以识别的 函数名。
服务器: 消息 195,级别 15,状态 1,过程 R_I_INOUTSTOR,行 49
'ROW_NUMBER' 不是可以识别的 函数名。
服务器: 消息 170,级别 15,状态 1,过程 R_I_INOUTSTOR,行 60
第 60 行: ')' 附近有语法错误。
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-28 20:45:24
-------------------------------------
--> 生成测试数据: [I_INSTORE]
IF OBJECT_ID('[I_INSTORE]') IS NOT NULL DROP TABLE [I_INSTORE]
CREATE TABLE [I_INSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_INSTORE]
SELECT 1001,'半成品仓','2009-07-01','090701001','16081D','个',300 UNION ALL
SELECT 1002,'半成品仓','2009-07-02','090702003','16081D','个',50 UNION ALL
SELECT 1003,'半成品仓','2009-07-18','090718003','16081D','个',90 UNION ALL
SELECT 1004,'半成品仓','2009-07-18','090718004','16081D','个',33 UNION ALL
SELECT 1009,'半成品仓','2009-07-01','090701002','16082D','个',50 UNION ALL
SELECT 1010,'半成品仓','2009-07-15','090715007','16083D','个',80 UNION ALL
SELECT 1020,'成品仓','2009-07-02','090702007','16081D','个',50 UNION ALL
SELECT 1022,'成品仓','2009-07-03','090703008','16081D','个',77
--> 生成测试数据: [I_OUTSTORE]
IF OBJECT_ID('[I_OUTSTORE]') IS NOT NULL DROP TABLE [I_OUTSTORE]
CREATE TABLE [I_OUTSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_OUTSTORE]
SELECT 3001,'半成品仓','2009-07-05','090705001','16081D','个',30 UNION ALL
SELECT 3002,'半成品仓','2009-07-18','090718002','16082D','个',20 UNION ALL
SELECT 3003,'半成品仓','2009-07-18','090718006','16082D','个',25 UNION ALL
SELECT 3004,'半成品仓','2009-07-23','090723007','16083D','个',35 UNION ALL
SELECT 3006,'成品仓','2009-07-28','090728026','16081D','个',20--SQL查询如下:GO
--2009-07-03 结束日期:2009-07-28CREATE PROC dbo.R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME
)
AS
DECLARE @p_tb TABLE(STORE VARCHAR(80), GOODS VARCHAR(80),UN VARCHAR(20),QT NUMERIC(10,2));
--计算期初数据
INSERT @p_tb
SELECT A.STORE,A.GOODS,A.UN,ISNULL(B.QT,0) AS QT
FROM (
SELECT STORE,GOODS,UN
FROM [I_INSTORE] GROUP BY STORE,GOODS,UN
) AS A
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_INSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS B
ON A. STORE=B.STORE AND A.GOODS=B.GOODS; --计算明细数据
DECLARE @tb TABLE(STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80),
UN VARCHAR(20), SQT NUMERIC(10,2), IQT NUMERIC(10,2) ,
OQT NUMERIC(10,2) ,CQT NUMERIC(10,2)); INSERT @tb
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT ISNULL(A.STORE,B.STORE) AS STORE,
ISNULL(A.BD,B.BD) AS BD,
ISNULL(A.BC,B.BC) AS BC,
ISNULL(A.GOODS,B.GOODS) AS GOODS,
ISNULL(A.UN,B.UN) AS UN,
0 AS SQT,
ISNULL(A.QT,0) AS IQT,
ISNULL(B.QT,0) AS OQT,
0 AS CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_INSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_INSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS A
FULL JOIN (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_OUTSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_OUTSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS B
ON A.GOODS=B.GOODS AND A.BD=B.BD AND A.STORE=B.STORE AND A.rowid=B.rowid
) AS T
--连接上期初数据和结存数据
UNION ALL
SELECT STORE,@FROMDATE,'期初',GOODS,UN,QT,0,0,QT FROM @p_tb
UNION ALL
SELECT STORE,@TODATE,'结存',GOODS,UN,QT,0,0,QT FROM @p_tb
) AS T
ORDER BY GOODS,STORE,BD; DECLARE @STORE VARCHAR(80),@GOODS VARCHAR(80);
DECLARE @CQT NUMERIC(10,2),@IQT NUMERIC(10,2),@OQT NUMERIC(10,2);
DECLARE @p_CQT NUMERIC(10,2); SELECT @CQT=0,@IQT=0,@OQT=0,@p_CQT=0; --更新库存
UPDATE @tb SET
@IQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN IQT + @IQT ELSE 0 END,
@OQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN OQT + @OQT ELSE 0 END ,
IQT = CASE WHEN BC = '结存' THEN @IQT ELSE IQT END,
OQT = CASE WHEN BC = '结存' THEN @OQT ELSE OQT END,
@p_CQT = @CQT,
@CQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS
THEN @CQT + IQT -OQT ELSE CQT END,
SQT = CASE WHEN BC IN('期初','结存') THEN SQT ELSE @p_CQT END,
CQT = @CQT,
@STORE = STORE,
@GOODS = GOODS; --查看数据
SELECT * FROM @tb;
GO EXEC dbo.R_I_INOUTSTOR '2009-07-03','2009-07-28'GO
--删除测试
DROP TABLE [I_INSTORE],[I_OUTSTORE];
DROP PROC dbo.R_I_INOUTSTOR;
EXEC dbo.R_I_INOUTSTOR '2009-07-03','2009-07-28'运行后的结果
半成品仓2009-07-03 期初 16081D 个 350.00 .00 .00 350.00
半成品仓2009-07-05 090705001 16081D 个 350.00 .00 30.00 320.00
半成品仓2009-07-18 090718003 16081D 个 320.00 90.00 .00 410.00
半成品仓2009-07-18 090718004 16081D 个 410.00 33.00 .00 443.00
半成品仓2009-07-28 结存 16081D 个 350.00 123.00 30.00 443.00
EXEC dbo.R_I_INOUTSTOR '2009-07-17','2009-07-28' 运行后的结果 期初应该为(350-30)=320
半成品仓2009-07-17 期初 16081D 个 350.00 .00 .00 350.00
半成品仓2009-07-18 090718003 16081D 个 350.00 90.00 .00 440.00
半成品仓2009-07-18 090718004 16081D 个 440.00 33.00 .00 473.00
半成品仓2009-07-28 结存 16081D 个 350.00 123.00 .00 473.00
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-28 20:45:24
-------------------------------------
--> 生成测试数据: [I_INSTORE]
IF OBJECT_ID('[I_INSTORE]') IS NOT NULL DROP TABLE [I_INSTORE]
CREATE TABLE [I_INSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_INSTORE]
SELECT 1001,'半成品仓','2009-07-01','090701001','16081D','个',300 UNION ALL
SELECT 1002,'半成品仓','2009-07-02','090702003','16081D','个',50 UNION ALL
SELECT 1003,'半成品仓','2009-07-18','090718003','16081D','个',90 UNION ALL
SELECT 1004,'半成品仓','2009-07-18','090718004','16081D','个',33 UNION ALL
SELECT 1009,'半成品仓','2009-07-01','090701002','16082D','个',50 UNION ALL
SELECT 1010,'半成品仓','2009-07-15','090715007','16083D','个',80 UNION ALL
SELECT 1020,'成品仓','2009-07-02','090702007','16081D','个',50 UNION ALL
SELECT 1022,'成品仓','2009-07-03','090703008','16081D','个',77
--> 生成测试数据: [I_OUTSTORE]
IF OBJECT_ID('[I_OUTSTORE]') IS NOT NULL DROP TABLE [I_OUTSTORE]
CREATE TABLE [I_OUTSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_OUTSTORE]
SELECT 3001,'半成品仓','2009-07-05','090705001','16081D','个',30 UNION ALL
SELECT 3002,'半成品仓','2009-07-18','090718002','16082D','个',20 UNION ALL
SELECT 3003,'半成品仓','2009-07-18','090718006','16082D','个',25 UNION ALL
SELECT 3004,'半成品仓','2009-07-23','090723007','16083D','个',35 UNION ALL
SELECT 3006,'成品仓','2009-07-28','090728026','16081D','个',20--SQL查询如下:GO
--2009-07-03 结束日期:2009-07-28CREATE PROC dbo.R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME
)
AS
DECLARE @p_tb TABLE(STORE VARCHAR(80), GOODS VARCHAR(80),UN VARCHAR(20),QT NUMERIC(10,2));
--计算期初数据
INSERT @p_tb
SELECT A.STORE,A.GOODS,A.UN,ISNULL(B.QT,0)-ISNULL(C.QT,0) AS QT
FROM (
SELECT STORE,GOODS,UN
FROM [I_INSTORE] GROUP BY STORE,GOODS,UN
) AS A
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_INSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS B
ON A. STORE=B.STORE AND A.GOODS=B.GOODS
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_OUTSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS C
on A. STORE=C.STORE AND A.GOODS=C.GOODS; --计算明细数据
DECLARE @tb TABLE(STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80),
UN VARCHAR(20), SQT NUMERIC(10,2), IQT NUMERIC(10,2) ,
OQT NUMERIC(10,2) ,CQT NUMERIC(10,2)); INSERT @tb
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT ISNULL(A.STORE,B.STORE) AS STORE,
ISNULL(A.BD,B.BD) AS BD,
ISNULL(A.BC,B.BC) AS BC,
ISNULL(A.GOODS,B.GOODS) AS GOODS,
ISNULL(A.UN,B.UN) AS UN,
0 AS SQT,
ISNULL(A.QT,0) AS IQT,
ISNULL(B.QT,0) AS OQT,
0 AS CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_INSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_INSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS A
FULL JOIN (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_OUTSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_OUTSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS B
ON A.GOODS=B.GOODS AND A.BD=B.BD AND A.STORE=B.STORE AND A.rowid=B.rowid
) AS T
--连接上期初数据和结存数据
UNION ALL
SELECT STORE,@FROMDATE,'期初',GOODS,UN,QT,0,0,QT FROM @p_tb
UNION ALL
SELECT STORE,@TODATE,'结存',GOODS,UN,QT,0,0,QT FROM @p_tb
) AS T
ORDER BY GOODS,STORE,BD; DECLARE @STORE VARCHAR(80),@GOODS VARCHAR(80);
DECLARE @CQT NUMERIC(10,2),@IQT NUMERIC(10,2),@OQT NUMERIC(10,2);
DECLARE @p_CQT NUMERIC(10,2); SELECT @CQT=0,@IQT=0,@OQT=0,@p_CQT=0; --更新库存
UPDATE @tb SET
@IQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN IQT + @IQT ELSE 0 END,
@OQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN OQT + @OQT ELSE 0 END ,
IQT = CASE WHEN BC = '结存' THEN @IQT ELSE IQT END,
OQT = CASE WHEN BC = '结存' THEN @OQT ELSE OQT END,
@p_CQT = @CQT,
@CQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS
THEN @CQT + IQT -OQT ELSE CQT END,
SQT = CASE WHEN BC IN('期初','结存') THEN SQT ELSE @p_CQT END,
CQT = @CQT,
@STORE = STORE,
@GOODS = GOODS; --查看数据
SELECT * FROM @tb;
GO EXEC dbo.R_I_INOUTSTOR '2009-07-17','2009-07-28'GO
--删除测试
DROP TABLE [I_INSTORE],[I_OUTSTORE];
DROP PROC dbo.R_I_INOUTSTOR;
EXEC dbo.R_I_INOUTSTOR '2009-07-02','2009-07-28'
结果如下: (同一开始日期 '2009-07-02' 这里的50个有问题,导致计算结果不正确)
半成品仓2009-07-02 090702003 16081D 个 .00 50.00 .00 .00
半成品仓2009-07-02 期初 16081D 个 300.00 .00 .00 .00
半成品仓2009-07-05 090705001 16081D 个 .00 .00 30.00 -30.00
半成品仓2009-07-18 090718003 16081D 个 -30.00 90.00 .00 60.00
半成品仓2009-07-18 090718004 16081D 个 60.00 33.00 .00 93.00
半成品仓2009-07-28 结存 16081D 个 300.00 123.00 30.00 93.00
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-28 20:45:24
-------------------------------------
--> 生成测试数据: [I_INSTORE]
IF OBJECT_ID('[I_INSTORE]') IS NOT NULL DROP TABLE [I_INSTORE]
CREATE TABLE [I_INSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_INSTORE]
SELECT 1001,'半成品仓','2009-07-01','090701001','16081D','个',300 UNION ALL
SELECT 1002,'半成品仓','2009-07-02','090702003','16081D','个',50 UNION ALL
SELECT 1002,'半成品仓','2009-07-02','090702009','16081D','个',80 UNION ALL
SELECT 1003,'半成品仓','2009-07-18','090718003','16081D','个',90 UNION ALL
SELECT 1004,'半成品仓','2009-07-18','090718004','16081D','个',33 UNION ALL
SELECT 1009,'半成品仓','2009-07-01','090701002','16082D','个',50 UNION ALL
SELECT 1010,'半成品仓','2009-07-15','090715007','16083D','个',80 UNION ALL
SELECT 1020,'成品仓','2009-07-02','090702007','16081D','个',50 UNION ALL
SELECT 1022,'成品仓','2009-07-03','090703008','16081D','个',77
--> 生成测试数据: [I_OUTSTORE]
IF OBJECT_ID('[I_OUTSTORE]') IS NOT NULL DROP TABLE [I_OUTSTORE]
CREATE TABLE [I_OUTSTORE] (BILLID INT,STORE VARCHAR(8),BD DATETIME,BC VARCHAR(9),GOODS VARCHAR(6),UN VARCHAR(2),QT INT)
INSERT INTO [I_OUTSTORE]
SELECT 1002,'半成品仓','2009-07-02','090702009','16081D','个',30 UNION ALL
SELECT 3001,'半成品仓','2009-07-05','090705001','16081D','个',30 UNION ALL
SELECT 3002,'半成品仓','2009-07-18','090718002','16082D','个',20 UNION ALL
SELECT 3003,'半成品仓','2009-07-18','090718006','16082D','个',25 UNION ALL
SELECT 3004,'半成品仓','2009-07-23','090723007','16083D','个',35 UNION ALL
SELECT 3006,'成品仓','2009-07-28','090728026','16081D','个',20--SQL查询如下:GO
--2009-07-03 结束日期:2009-07-28CREATE PROC dbo.R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME
)
AS
DECLARE @p_tb TABLE(STORE VARCHAR(80), GOODS VARCHAR(80),UN VARCHAR(20),QT NUMERIC(10,2));
--计算期初数据
INSERT @p_tb
SELECT A.STORE,A.GOODS,A.UN,ISNULL(B.QT,0)-ISNULL(C.QT,0) AS QT
FROM (
SELECT STORE,GOODS,UN
FROM [I_INSTORE] GROUP BY STORE,GOODS,UN
) AS A
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_INSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS B
ON A. STORE=B.STORE AND A.GOODS=B.GOODS
LEFT JOIN (
SELECT STORE,GOODS,UN,SUM(QT) AS QT
FROM [I_OUTSTORE]
WHERE BD < @FROMDATE GROUP BY STORE,GOODS,UN
) AS C
on A. STORE=C.STORE AND A.GOODS=C.GOODS; --计算明细数据
DECLARE @tb TABLE(STORE VARCHAR(80), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80),
UN VARCHAR(20), SQT NUMERIC(10,2), IQT NUMERIC(10,2) ,
OQT NUMERIC(10,2) ,CQT NUMERIC(10,2)); INSERT @tb
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT
FROM (
SELECT STORE,BD,BC,GOODS,UN,SQT,IQT,OQT,CQT,flag
FROM (
SELECT ISNULL(A.STORE,B.STORE) AS STORE,
ISNULL(A.BD,B.BD) AS BD,
ISNULL(A.BC,B.BC) AS BC,
ISNULL(A.GOODS,B.GOODS) AS GOODS,
ISNULL(A.UN,B.UN) AS UN,
0 AS SQT,
ISNULL(A.QT,0) AS IQT,
ISNULL(B.QT,0) AS OQT,
0 AS CQT,
1 AS flag
FROM (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_INSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_INSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS A
FULL JOIN (
SELECT STORE,BD,BC,GOODS,UN,QT,
--rowid=ROW_NUMBER() OVER(PARTITION BY GOODS,STORE,BD ORDER BY BC)
rowid=(SELECT COUNT(*) FROM [I_OUTSTORE]
WHERE A.GOODS=GOODS AND A.STORE=STORE
AND A.BD=BD AND BC < A.BC)
FROM [I_OUTSTORE] AS A
WHERE BD >= @FROMDATE AND BD < DATEADD(day,1,@TODATE)
) AS B
ON A.GOODS=B.GOODS AND A.BD=B.BD AND A.STORE=B.STORE AND A.rowid=B.rowid
) AS T
--连接上期初数据和结存数据
UNION ALL
SELECT STORE,@FROMDATE,'期初',GOODS,UN,QT,0,0,QT,0 FROM @p_tb
UNION ALL
SELECT STORE,@TODATE,'结存',GOODS,UN,QT,0,0,QT,2 FROM @p_tb
) AS T
ORDER BY GOODS,STORE,BD,flag; DECLARE @STORE VARCHAR(80),@GOODS VARCHAR(80);
DECLARE @CQT NUMERIC(10,2),@IQT NUMERIC(10,2),@OQT NUMERIC(10,2);
DECLARE @p_CQT NUMERIC(10,2); SELECT @CQT=0,@IQT=0,@OQT=0,@p_CQT=0; --更新库存
UPDATE @tb SET
@IQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN IQT + @IQT ELSE 0 END,
@OQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS THEN OQT + @OQT ELSE 0 END ,
IQT = CASE WHEN BC = '结存' THEN @IQT ELSE IQT END,
OQT = CASE WHEN BC = '结存' THEN @OQT ELSE OQT END,
@p_CQT = @CQT,
@CQT = CASE WHEN STORE=@STORE AND GOODS=@GOODS
THEN @CQT + IQT -OQT ELSE CQT END,
SQT = CASE WHEN BC IN('期初','结存') THEN SQT ELSE @p_CQT END,
CQT = @CQT,
@STORE = STORE,
@GOODS = GOODS; --查看数据
SELECT * FROM @tb;
GO EXEC dbo.R_I_INOUTSTOR '2009-07-02','2009-07-28'GO
--删除测试
DROP TABLE [I_INSTORE],[I_OUTSTORE];
DROP PROC dbo.R_I_INOUTSTOR;
请大家再帮忙测试,希望能做成函数方便原软件报表调用(非存储过程)temp 表是select * into temp from
(select store ,bd, BC,GOODS,UN,QT , 0 ot from I_INSTORE
union
select store ,bd, BC,GOODS,UN,QT , 0 ot from I_OUTSTORE) ccreate function [dbo].[R_I_INOUTSTOR](@FROMDATE datetime, @TODATE datetime)
RETURNS @TempTable TABLE (STORE VARCHAR(50), BD DATETIME, BC VARCHAR(80),GOODS VARCHAR(80) , UN VARCHAR(4), SQT FLOAT , IQT FLOAT , OQT FLOAT ,CQT FLOAT)
as
begin
--declare @sqlString varchar(8000)
declare @I_STORE varchar(50)
declare @I_GOODS varchar(50)
declare @I_UN char(2)
declare @II_STORE varchar(50)
declare @II_GOODS varchar(50)
declare @II_UN char(2)
declare @II_BD datetime
declare @II_BC varchar(50)
declare @II_QT int
declare @II_ot int
declare @iqt int
declare @oqt int
declare @sqt int
declare @cqt int
declare I_cursor cursor for
select store,goods,un from temp
group by store,goods,un
order by store, goods
open I_cursor
fetch next from I_cursor into @I_STORE,@I_GOODS, @I_UN
while @@FETCH_STATUS=0
begin
if exists (select 1 from temp where store=@I_store and goods=@I_goods and un=@I_un and bd<@fromdate )
set @sqt=(select sum(qt) from temp where store=@I_store and goods=@I_goods and un=@I_un and bd<@fromdate )
else
set @sqt=0
set @iqt=0
set @oqt=0
set @cqt=@sqt+@iqt-@oqt
set @II_bc='''期初'''
set @II_BD=@fromdate
insert into @TempTable values(@I_store,@II_BD,@II_bc ,@I_Goods,@I_un,@sqt,@iqt,@oqt,@cqt)
declare II_cursor cursor for
select bd,bc,qt,ot from temp where store=@I_store and goods=@I_goods and un=@I_un and bd between @fromdate and @todate
open II_cursor
fetch next from II_cursor into @II_BD, @II_BC,@II_QT ,@II_ot
while @@fetch_status=0
begin
set @sqt=@sqt
set @iqt=@II_qt
set @oqt=@II_ot
set @cqt=@sqt+@iqt-@oqt
insert into @TempTable values(@I_store,@II_BD,@II_bc ,@I_Goods,@I_un,@sqt,@iqt,@oqt,@cqt) fetch next from II_cursor into @II_BD, @II_BC,@II_QT ,@II_ot
set @sqt=@cqt
end
close II_cursor
deallocate II_cursor
if exists (select 1 from temp where store=@I_store and goods=@I_goods and un=@I_un and bd<@fromdate )
set @sqt=(select sum(qt) from temp where store=@I_store and goods=@I_goods and un=@I_un and bd<@fromdate )
else
set @sqt=0
set @iqt=(select sum(qt) from temp where store=@I_store and goods=@I_goods and un=@I_un and bd between @fromdate and @todate )
set @oqt=(select sum(ot) from temp where store=@I_store and goods=@I_goods and un=@I_un and bd between @fromdate and @todate )
set @II_BD=@todate
set @II_BC='''结存'''
insert into @TempTable values(@I_store,@II_BD,@II_bc ,@I_Goods,@I_un,@sqt,@iqt,@oqt,@cqt) fetch next from I_cursor into @I_STORE,@I_GOODS, @I_UN
end
close I_cursor
deallocate I_cursor
return
end
returns @rtb TABLE
(
[STORE] [nvarchar](20) COLLATE DATABASE_DEFAULT NULL,
[BD] [smalldatetime] NULL,
[BC] [nvarchar](20) COLLATE DATABASE_DEFAULT NULL,
[GOODS] [nvarchar](20) COLLATE DATABASE_DEFAULT NULL,
[UN] [nvarchar](20) COLLATE DATABASE_DEFAULT NULL,
[SQT] [int] NULL,
[IQT] [int] NULL,
[OQT] [int] NULL,
[CQT] [int] NULL
)
AS
BEGIN
DECLARE @STORE NVARCHAR(20), @GOODS NVARCHAR(20), @SQT INT , @IQT INT , @OQT INT, @CQT INT;
DECLARE @STORE_1 NVARCHAR(20), @BD_1 SMALLDATETIME, @GOODS_1 NVARCHAR(20), @BC_1 NVARCHAR(20), @QT_1 INT, @TP_1 INT;--STEP 1 : 根据开始日期生成所有期初记录
INSERT INTO @rtb
SELECT QICHU.[STORE], @START_DATE, N' 期初', --为了方便排序, 使 '期初' 的数据在其他的数据前面, 特意在前面加了个空格, 否则要多加个字段来设置
QICHU.[GOODS], N'个', QICHU.[SQT], 0, 0, QICHU.[SQT]
FROM
(
SELECT BAS.[STORE], BAS.[GOODS],
ISNULL((SELECT SUM(T.QT) FROM [I_INSTORE] AS T WHERE T.[STORE]=BAS.[STORE] AND T.[GOODS]=BAS.[GOODS] AND T.[BD]<@START_DATE) ,0) AS SQT
FROM [I_INSTORE] AS BAS group by BAS.[store] , BAS.[goods]
)
AS QICHU ;--STEP 2 : 根据结束日期生成所有结存记录
INSERT INTO @rtb
SELECT JIECUN.[STORE], @END_DATE, N'结存', JIECUN.[GOODS], N'个',
JIECUN.[SQT], JIECUN.[IQT], JIECUN.[OQT], (JIECUN.[SQT] + JIECUN.[IQT] - JIECUN.[OQT])
FROM
(
SELECT BAS.[STORE], BAS.[GOODS],
ISNULL((SELECT SUM(T.QT) FROM [I_INSTORE] AS T WHERE T.[STORE]=BAS.[STORE] AND T.[GOODS]=BAS.[GOODS] AND T.[BD]<@START_DATE) ,0) AS SQT,
ISNULL((SELECT SUM(T.QT) FROM [I_INSTORE] AS T WHERE T.[STORE]=BAS.[STORE] AND T.[GOODS]=BAS.[GOODS] AND T.[BD]>=@START_DATE AND T.[BD]<=@END_DATE) ,0) AS IQT,
ISNULL((SELECT SUM(T.QT) FROM [I_OUTSTORE] AS T WHERE T.[STORE]=BAS.[STORE] AND T.[GOODS]=BAS.[GOODS] AND T.[BD]>=@START_DATE AND T.[BD]<=@END_DATE) ,0) AS OQT
FROM [I_INSTORE] AS BAS group by BAS.[store] , BAS.[goods]
)
AS JIECUN ;--STEP 3 : 开始干活 declare c1 CURSOR LOCAL FAST_FORWARD
FOR (
select [STORE],[GOODS],[CQT] FROM @rtb WHERE [BC]=N' 期初'
); OPEN c1;
FETCH NEXT FROM c1 INTO @STORE,@GOODS,@SQT ; WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR LOCAL FAST_FORWARD
FOR
(
select T.[STORE],T.[BD],T.[GOODS],T.[BC],T.[QT], T.[TP]
FROM
(
select [STORE],[BD],[GOODS],[BC],[QT], 0 AS TP --0 表示数据是来自[I_INSTORE]
FROM [I_INSTORE] WHERE [STORE]=@STORE AND [GOODS]=@GOODS AND [BD]>=@START_DATE AND [BD]<=@END_DATE
UNION
select [STORE],[BD],[GOODS],[BC],[QT],1 AS TP --1 表示数据是来自[I_OUTSTORE]
FROM [I_OUTSTORE] WHERE [STORE]=@STORE AND [GOODS]=@GOODS AND [BD]>=@START_DATE AND [BD]<=@END_DATE
) AS T GROUP BY T.[STORE],T.[BD],T.[GOODS],T.[BC],T.[QT], T.[TP]
);
OPEN c2;
FETCH NEXT FROM c2 INTO @STORE_1,@BD_1, @GOODS_1, @BC_1, @QT_1,@TP_1 ;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TP_1=0
BEGIN
SET @IQT = @QT_1;
SET @OQT = 0;
SET @CQT = @SQT + @QT_1;
END
ELSE
BEGIN
SET @IQT = 0;
SET @OQT = @QT_1;
SET @CQT = @SQT - @QT_1;
END
INSERT INTO @rtb([STORE], [BD], [BC], [GOODS], UN, SQT, IQT, OQT, CQT)
VALUES(@STORE_1, @BD_1, @BC_1, @GOODS_1, N'个', @SQT, @IQT, @OQT, @CQT); SET @SQT = @CQT ;
FETCH NEXT FROM c2 INTO @STORE_1,@BD_1, @GOODS_1, @BC_1, @QT_1,@TP_1 ;
END CLOSE c2;
DEALLOCATE c2; FETCH NEXT FROM c1 INTO @STORE,@GOODS,@SQT ;
END CLOSE c1;
DEALLOCATE c1;--STEP 4 : 结束, 返回@rtb return ;
END具体的使用
select * from dbo.FUNC_GET_INOUT_DETAIL('2009-07-03','2009-07-28')
order by [STORE], [GOODS],[BD],[BC] ;如有问题, 高知一下.
期初是不是应该包含开始日期前的进仓和开始日期前的出仓??
--STEP 1 : 根据开始日期生成所有期初记录
INSERT INTO @rtb
SELECT QICHU.[STORE], @START_DATE, N' 期初', --为了方便排序, 使 '期初' 的数据在其他的数据前面, 特意在前面加了个空格, 否则要多加个字段来设置
QICHU.[GOODS], N'个', QICHU.[SQT], 0, 0, QICHU.[SQT]
FROM
(
SELECT BAS.[STORE], BAS.[GOODS],
ISNULL((SELECT SUM(T.QT) FROM [I_INSTORE] AS T WHERE T.[STORE]=BAS.[STORE] AND T.[GOODS]=BAS.[GOODS] AND T.[BD]<@START_DATE) ,0) AS SQT
FROM [I_INSTORE] AS BAS group by BAS.[store] , BAS.[goods]
)
AS QICHU ;
应该有个库存表吧?至少要记录当前的库存的。
除非每天到最后保证库存为0,否则这样的算法是不正确的。
最好还有个日结转表,记录每天业务结束后的库存。以前我做的那个就是这样做的,表列还多,包括产品,日期,部门,期初库存,期初金额,购进,销售,领用,损耗,溢出,本期结存等的数量和金额。
基本思路就是如上,有库存,和每日的结存信息,这个也在需要引起轰动的表中。大体做法就是先算出一种,然后再依次更新。更新的方法也有些问题的,不有从入库表中查。这样,如果出库表的产品在入库中没有记录。那么就查不到这种产品的数据了。
这个需要从产品表中查的,在最后把所有数量为0的多余记录删除,才能得到最终的结果。现在看来,是不是可以利用子查询来一次性得到所有遥结果,如下:
select ...(select sum(qty) from outstock where ...) as outQty
from products ,instock
但语句应该长的吓人
当然在这语句后,还要把没有业务操作的产品删除函数应该是可以做的吧,只要做成一个表函数的形式是不是就可以了。
代码我不不写了,有的是高手。