一、业务背景
今天是2024年07月30日，系统2024年01月01日启用，导入初始库存。
二、需求背景
需要快速查询库存组织=供应链中心下，某仓库某物料的库存数。后面还需要按照过去时间范围查询每一天的库存量以监控变化。
三、参考
《库存汇总表》《库存余额》《库存账龄分析》《物料收发明细表》
四、开发步骤
1、创建单据《历史库存信息》
字段：日期，单据编号，数据状态，库存组织，仓库，物料编码，库存数，锁库数
表结构
CREATE TABLE [XXXX_T_STK_HisInventory](
[FID] [BIGINT] NOT NULL PRIMARY KEY,
[FBILLNO] [NVARCHAR](30) NOT NULL,
[FDOCUMENTSTATUS] [CHAR](1) NOT NULL,
[F_XXXX_DATE] [DATETIME] NULL,
[F_XXXX_CREATEDATE] [DATETIME] NULL,
[F_XXXX_CREATORID] [INT] NOT NULL,
[F_XXXX_APPROVEDATE] [DATETIME] NULL,
[F_XXXX_MODIFYDATE] [DATETIME] NULL,
[F_XXXX_MODIFIERID] [INT] NOT NULL,
[F_XXXX_APPROVERID] [INT] NOT NULL,
[F_XXXX_STOCKORGID] [INT] NOT NULL,
[F_XXXX_STOCKID] [INT] NOT NULL,
[F_XXXX_STOCKQTY] [DECIMAL](23, 10) NOT NULL,
[F_XXXX_SYSLOCKQTY] [DECIMAL](23, 10) NOT NULL,
[F_XXXX_MATERIALID] [INT] NOT NULL)
2、需要生成日期库存信息的日期范围存储为表
后台生成2024-01-01至2024-07-21之间的数据。
#### 创建日期表
CREATE TABLE date_tables2 (
date_column DATE PRIMARY KEY,--库存日期
begindate DATE,--关账后第一天
enddate DATE--库存日期+1
);
#### 使用递归插入数据
;WITH DateRange AS ( SELECT '2024-01-01' AS DateValue UNION ALL SELECT CONVERT(VARCHAR(10),DATEADD(Day,1,DateValue) ,23) AS DateValue FROM DateRange WHERE DateValue<='2024-07-20' ) INSERT INTO date_tables2 SELECT DateValue ,CONVERT(VARCHAR(10) ,dateadd(day,-day(DateValue)+1,DateValue),120) ,DATEADD(DAY,1,DateValue) FROM DateRange OPTION(MAXRECURSION 0)
注意如果报错“语句被终止。完成执行语句前已用完最大递归 100。”
解决方案 是 添加 OPTION(MAXRECURSION 0)
3、创建存储过程1，根据日期生成计算当日库存，生成《历史库存信息》
CREATE PROCEDURE [dbo].[XXXX_PR_STK_STOCKQTYALLGENERALINIT](
@DTIME VARCHAR(20),
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20)
)
AS
BEGIN
DECLARE @BALTYPE INT
SET @BALTYPE=0
--获取当前关账的最后日期
DECLARE @fclosedate1 VARCHAR(10);
SET @fclosedate1=CONVERT(VARCHAR(10), DATEADD(D,-1,@BEGINTIME),23)
--PRINT '@fclosedate1:'+@fclosedate1
--SELECT CONVERT(VARCHAR(10), DATEADD(D,-1,'2024-01-01'),23)
IF(@BEGINTIME='2024-01-01')
BEGIN
SET @fclosedate1=@BEGINTIME
SET @BALTYPE=1
END
DECLARE @DATE DATETIME
SET @DATE=CAST(@DTIME AS DATETIME)
SET @BEGINTIME=@BEGINTIME+' 00:00:00'
SET @ENDTIME=@ENDTIME+' 00:00:00'
PRINT @BEGINTIME
PRINT @ENDTIME
--SELECT LEN('2024-06-01 00:00:00')
--DECLARE @BEGINTIME VARCHAR(20)
--SET @BEGINTIME='2024-06-01 00:00:00'
--DECLARE @ENDTIME VARCHAR(20)
--SET @ENDTIME='2024-06-03 00:00:00'
--DECLARE @STOCKID INT
--SET @STOCKID=493513
--获取当前关账最后日期
--DECLARE @fclosedate1 VARCHAR(10);
--SET @fclosedate1=(SELECT CONVERT(VARCHAR(10), MAX(FCLOSEDATE),23) fclosedate
--FROM T_STK_CLOSEPROFILE WHERE ((FORGID = (100006) AND FCATEGORY = 'STK')
--AND (FCLOSEDATE < '2024-06-01 00:00:00')) GROUP BY FORGID)
--PRINT '@fclosedate1:'+ @fclosedate1
SELECT m.fmaterialid,m.fstockid,SUM(m.fqtyaddoption*m.fbaseqty) fqty
INTO #m
FROM (
SELECT 'SAL_OUTSTOCK' fformid
, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
,t2.FSTOCKID
, t2.FBASEUNITQTY fbaseqty
FROM T_SAL_OUTSTOCK t0
LEFT OUTER JOIN T_SAL_OUTSTOCKFIN t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_SAL_OUTSTOCKENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st238 ON t2.FMATERIALID = st238.FMATERIALID
LEFT OUTER JOIN t_BD_MaterialBase st239 ON t2.FMATERIALID = st239.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((t1.FISGENFORIOS = '0' AND (st238.FERPCLSID <> '6'))
AND (st239.FSUITE <> '1'))) AND t0.FOBJECTTYPEID = 'SAL_OUTSTOCK') AND t0.FCANCELSTATUS = 'A')
--AND t2.FSTOCKID=@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'SAL_RETURNSTOCK' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEUNITQTY fbaseqty
FROM T_SAL_RETURNSTOCK t0 LEFT OUTER JOIN T_SAL_RETURNSTOCKENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_SAL_RETURNSTOCKFIN t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN T_BD_MATERIAL st11 ON t1.FMATERIALID = st11.FMATERIALID
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st129 ON t1.FMATERIALID = st129.FMATERIALID
LEFT OUTER JOIN t_BD_MaterialBase st130 ON t1.FMATERIALID = st130.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (((t2.FISGENFORIOS = '0' AND (st129.FERPCLSID <> '6'))
AND (st130.FSUITE <> '1')) AND (t1.FRETURNTYPE <> '0fa6270ab70b416cb2a7141a8f182d64')))
AND t0.FOBJECTTYPEID = 'SAL_RETURNSTOCK') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID=@STOCKID
--AND t1.FMATERIALID IN (SELECT FMATERIALID FROM dbo.T_BD_MATERIAL WHERE fnumber=@MATERNUMBER)
--AND t1.FMATERIALID=669072
--AND ((((st11.FNUMBER >= '015102042') AND (st11.FNUMBER <= '015102042'))
--AND (st17.FNUMBER >= '02')) AND (st17.FNUMBER <= '02')))
)
UNION ALL
SELECT 'STK_InStock' fformid
, 1 fqtyaddoption --1 入库 0出库
, t2.FMATERIALID fmaterialid
,t2.FSTOCKID
,t2.FBASEUNITQTY fbaseqty
FROM t_STK_InStock t0 LEFT OUTER JOIN t_STK_InStockFin t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_INSTOCKENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st24 ON t2.FSTOCKID = st24.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st241 ON t2.FMATERIALID = st241.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006)
AND (t1.FISGENFORIOS = '0' AND st241.FISINVENTORY = 1))
AND t0.FOBJECTTYPEID = 'STK_InStock')
AND t0.FCANCELSTATUS = 'A')
--AND t2.FSTOCKID=@STOCKID
--AND ((st24.FNUMBER >= '02')
--AND (st24.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_InStock' fformid
, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
,t2.FRECEIVESTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_STK_InStock t0
LEFT OUTER JOIN t_STK_InStockFin t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_INSTOCKENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st228 ON t2.FRECEIVESTOCKID = st228.FStockId
WHERE ((((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006)
AND (((t2.FRECEIVESTOCKSTATUS <> 0)
AND t2.FSRCBILLTYPEID = 'PUR_ReceiveBill')
AND t1.FISGENFORIOS = '0')) AND t0.FOBJECTTYPEID = 'STK_InStock')
AND t0.FCANCELSTATUS = 'A')
--AND t2.FRECEIVESTOCKID =@STOCKID
--AND ((st228.FNUMBER >= '02') AND (st228.FNUMBER <= '02'))
)
AND t2.FRECEIVESTOCKFLAG = '1')
UNION ALL
SELECT 'PRD_PickMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t1.FSTOCKID fstockid
, t1_A.FBASESTOCKACTUALQTY fbaseqty
FROM T_PRD_PICKMTRL t0
LEFT OUTER JOIN T_PRD_PICKMTRLDATA t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PRD_PICKMTRLDATA_A t1_A ON t1.FENTRYID = t1_A.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((t1_A.FBASESTOCKACTUALQTY <> 0) OR (t1.FSECACTUALQTY <> 0)))
AND t0.FFORMID = 'PRD_PickMtrl') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PRD_FeedMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t1.FSTOCKID
, t1_Q.FBASESTOCKACTUALQTY fbaseqty
FROM T_PRD_FEEDMTRL t0
LEFT OUTER JOIN T_PRD_FEEDMTRLDATA t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PRD_FEEDMTRLDATA_Q t1_Q ON t1.FENTRYID = t1_Q.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st11 ON t1.FSTOCKID = st11.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((t1_Q.FBASESTOCKACTUALQTY <> 0) OR (t1_Q.FSECACTUALQTY <> 0)))
AND t0.FFORMID = 'PRD_FeedMtrl') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st11.FNUMBER >= '02') AND (st11.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_StockCountLoss' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASELOSSQTY fbaseqty
FROM T_STK_STKCOUNTLOSS t0
LEFT OUTER JOIN T_STK_STKCOUNTLOSSENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FOBJECTTYPEID = 'STK_StockCountLoss') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MISCELLANEOUS' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISCELLANEOUS t0
LEFT OUTER JOIN T_STK_MISCELLANEOUSENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'GENERAL')
AND t0.FOBJECTTYPEID = 'STK_MISCELLANEOUS')
AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MISCELLANEOUS' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISCELLANEOUS t0
LEFT OUTER JOIN T_STK_MISCELLANEOUSENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'RETURN')
AND t0.FOBJECTTYPEID = 'STK_MISCELLANEOUS') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MisDelivery' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISDELIVERY t0
LEFT OUTER JOIN T_STK_MISDELIVERYENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'GENERAL')
AND t0.FOBJECTTYPEID = 'STK_MisDelivery') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_MisDelivery' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_MISDELIVERY t0
LEFT OUTER JOIN T_STK_MISDELIVERYENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FSTOCKDIRECT = 'RETURN')
AND t0.FOBJECTTYPEID = 'STK_MisDelivery') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--ND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PUR_ReceiveBill' fformid, 1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM T_PUR_Receive t0
LEFT OUTER JOIN T_PUR_ReceiveEntry t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FOBJECTTYPEID = 'PUR_ReceiveBill') AND t0.FCANCELSTATUS = 'A')
--AND t2.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
AND t2.FSTOCKFLAG = '1')
UNION ALL
SELECT 'STK_AssembledApp' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st19 ON t1.FSTOCKID = st19.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Assembly')
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st19.FNUMBER >= '02') AND (st19.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_ASSEMBLYSUBITEM t2 ON t1.FENTRYID = t2.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Assembly')
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st19 ON t1.FSTOCKID = st19.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Dassembly')
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st19.FNUMBER >= '02') AND (st19.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_AssembledApp' fformid, 1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEQTY fbaseqty
FROM T_STK_ASSEMBLY t0
LEFT OUTER JOIN T_STK_ASSEMBLYPRODUCT t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_ASSEMBLYSUBITEM t2 ON t1.FENTRYID = t2.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st28 ON t2.FSTOCKID = st28.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FAFFAIRTYPE = 'Dassembly')
AND t0.FOBJECTTYPEID = 'STK_AssembledApp') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st28.FNUMBER >= '02') AND (st28.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FISGENFORIOS = '0') AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT')
AND t0.FCANCELSTATUS = 'A')
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, 1 fqtyaddoption
, t1.FDESTMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME)
AND (t0.FDATE < @ENDTIME)) AND t0.FSTOCKINORGID = 100006)
AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'B'))
AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT') AND t0.FCANCELSTATUS = 'A')
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFEROUT' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFEROUT t0
LEFT OUTER JOIN T_STK_STKTRANSFEROUTENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'A'))
AND t0.FOBJECTTYPEID = 'STK_TRANSFEROUT') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_StockConvert' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_StockConvert t0
LEFT OUTER JOIN T_STK_StockConvertEntry t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'B') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_StockConvert' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_StockConvert t0
LEFT OUTER JOIN T_STK_StockConvertEntry t1 ON t0.FID = t1.FID
LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE ((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'A') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFERIN' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND (t1.FBASEQTY <> 0)))
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A')
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFERIN' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1_T.FBASETRANSFERQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY_T t1_T ON t1.FENTRYID = t1_T.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FDESTSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'B'))
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A')
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TRANSFERIN' fformid, -1 fqtyaddoption
, t1.FSRCMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1_T.FBASETRANSFERQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY_T t1_T ON t1.FENTRYID = t1_T.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st14 ON t1.FSRCSTOCKID = st14.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKOUTORGID = 100006) AND (t0.FISGENFORIOS = '0' AND t0.FVESTONWAY = 'A'))
AND t0.FOBJECTTYPEID = 'STK_TRANSFERIN') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st14.FNUMBER >= '02') AND (st14.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PRD_ReturnMtrl' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1_A.FBASESTOCKQTY fbaseqty
FROM T_PRD_RETURNMTRL t0
LEFT OUTER JOIN T_PRD_RETURNMTRLENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PRD_RETURNMTRLENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSTOCKID = st15.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((t1_A.FBASESTOCKQTY <> 0) OR (t1.FSECSTOCKQTY <> 0)))
AND t0.FFORMID = 'PRD_ReturnMtrl') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_LOTADJUST' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_LOTADJUST t0
LEFT OUTER JOIN T_STK_LOTADJUSTENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st16 ON t1.FSTOCKID = st16.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'A') AND t0.FOBJECTTYPEID = 'STK_LOTADJUST')
AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st16.FNUMBER >= '02') AND (st16.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_LOTADJUST' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_LOTADJUST t0
LEFT OUTER JOIN T_STK_LOTADJUSTENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st16 ON t1.FSTOCKID = st16.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t1.FCONVERTTYPE = 'B') AND t0.FOBJECTTYPEID = 'STK_LOTADJUST')
AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st16.FNUMBER >= '02') AND (st16.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PRD_INSTOCK' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASEREALQTY fbaseqty
FROM T_PRD_INSTOCK t0
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND t0.FENTRUSTINSTOCKID = 0)
AND t0.FFORMID = 'PRD_INSTOCK') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TransferDirect' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FDESTSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st110 ON t1.FDESTSTOCKID = st110.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st133 ON t1.FSRCMATERIALID = st133.FMATERIALID
LEFT OUTER JOIN t_BD_MaterialBase st134 ON t1.FSRCMATERIALID = st134.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((st133.FERPCLSID <> '6') AND (st134.FSUITE <> '1')))
AND t0.FOBJECTTYPEID = 'STK_TransferDirect') AND t0.FCANCELSTATUS = 'A')
--AND t1.FDESTSTOCKID =@STOCKID
--AND ((st110.FNUMBER >= '02') AND (st110.FNUMBER <= '02'))
)
UNION ALL
SELECT 'STK_TransferDirect' fformid, -1 fqtyaddoption
, t1.FSRCMATERIALID fmaterialid
, t1.FSRCSTOCKID fstockid
, t1.FBASEQTY fbaseqty
FROM T_STK_STKTRANSFERIN t0
LEFT OUTER JOIN T_STK_STKTRANSFERINENTRY t1 ON t0.FID = t1.FID
--LEFT OUTER JOIN t_BD_Stock st15 ON t1.FSRCSTOCKID = st15.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st133 ON t1.FSRCMATERIALID = st133.FMATERIALID
LEFT OUTER JOIN t_BD_MaterialBase st134 ON t1.FSRCMATERIALID = st134.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKOUTORGID = 100006) AND ((st133.FERPCLSID <> '6') AND (st134.FSUITE <> '1')))
AND t0.FOBJECTTYPEID = 'STK_TransferDirect') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSRCSTOCKID =@STOCKID
--AND ((st15.FNUMBER >= '02') AND (st15.FNUMBER <= '02'))
)
UNION ALL
SELECT 'SUB_RETURNMTRL' fformid, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASESTOCKQTY fbaseqty
FROM T_SUB_RETURNMTRL t0
LEFT OUTER JOIN T_SUB_RETURNMTRLENTRY t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_SUB_RETURNMTRLENTRY_A t1_A ON t1.FENTRYID = t1_A.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006)
AND ((t1.FBASESTOCKQTY <> 0) OR (FSECQTY <> 0))) AND t0.FFORMID = 'SUB_RETURNMTRL')
AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)
UNION ALL
SELECT 'SUB_PickMtrl' fformid, -1 fqtyaddoption
, t1.FMATERIALID fmaterialid
, t1.FSTOCKID fstockid
, t1.FBASESTOCKACTUALQTY fbaseqty
FROM T_SUB_PICKMTRL t0
LEFT OUTER JOIN T_SUB_PICKMTRLDATA t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_SUB_PICKMTRLDATA_A t1_A ON t1.FENTRYID = t1_A.FENTRYID
--LEFT OUTER JOIN t_BD_Stock st111 ON t1.FSTOCKID = st111.FStockId
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND ((t1.FBASESTOCKACTUALQTY <> 0) OR (t1.FSECACTUALQTY <> 0)))
AND t0.FFORMID = 'SUB_PickMtrl') AND t0.FCANCELSTATUS = 'A')
--AND t1.FSTOCKID =@STOCKID
--AND ((st111.FNUMBER >= '02') AND (st111.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PUR_MRB' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FSTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_PUR_MRB t0
LEFT OUTER JOIN T_PUR_MRBFIN t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PUR_MRBENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st26 ON t2.FSTOCKID = st26.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st236 ON t2.FMATERIALID = st236.FMATERIALID
WHERE (((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (((st236.FISINVENTORY = 1 AND (t2.FSTOCKSTATUSID <> t2.FRECEIVESTOCKSTATUSID))
AND t1.FISGENFORIOS = '0') AND t0.FMRTYPE = 'B'))
AND t0.FOBJECTTYPEID = 'PUR_MRB') AND t0.FCANCELSTATUS = 'A')
--AND t2.FSTOCKID =@STOCKID
--AND ((st26.FNUMBER >= '02') AND (st26.FNUMBER <= '02'))
)
UNION ALL
SELECT 'PUR_MRB' fformid, -1 fqtyaddoption
, t2.FMATERIALID fmaterialid
, t2.FRECEIVESTOCKID fstockid
, t2.FBASEUNITQTY fbaseqty
FROM t_PUR_MRB t0
LEFT OUTER JOIN T_PUR_MRBFIN t1 ON t0.FID = t1.FID
LEFT OUTER JOIN T_PUR_MRBENTRY t2 ON t0.FID = t2.FID
--LEFT OUTER JOIN t_BD_Stock st225 ON t2.FRECEIVESTOCKID = st225.FStockId
LEFT OUTER JOIN t_BD_MaterialBase st236 ON t2.FMATERIALID = st236.FMATERIALID
WHERE ((((((((t0.FDATE >= @BEGINTIME) AND (t0.FDATE < @ENDTIME))
AND t0.FSTOCKORGID = 100006) AND (((st236.FISINVENTORY = 1
AND (t2.FRECEIVESTOCKSTATUSID <> 0)) AND t2.FSTOCKSTATUSID = t2.FRECEIVESTOCKSTATUSID)
AND t1.FISGENFORIOS = '0')) AND t0.FOBJECTTYPEID = 'PUR_MRB')
AND t0.FCANCELSTATUS = 'A')
--AND t2.FRECEIVESTOCKID =@STOCKID
--AND ((st225.FNUMBER >= '02') AND (st225.FNUMBER <= '02'))
)
AND t2.FRECEIVESTOCKFLAG = '1')
UNION ALL
SELECT 'STK_INVBAL' fformid
, 1 fqtyaddoption
, t1.FMATERIALID fmaterialid
,t.FSTOCKID
, SUM(t.FBASEQTY) fbaseqty
FROM (
SELECT TM.FNUMBER
,ISNULL(TI.FBASEENDQTY,0) FBASEQTY
,ISNULL(TI.FSECENDQTY,0) FSECQTY,TI.FMATERIALID FMATERIALID1
,TI.FSTOCKORGID
,TI.FAUXPROPID,TI.FKEEPERID,TI.FKEEPERTYPEID,TI.FLOT,TI.FOWNERID
,TI.FOWNERTYPEID,TI.FSTOCKID,TI.FSTOCKLOCID
,TI.FSTOCKSTATUSID,TI.FBOMID,TI.FMTONO,TI.FPROJECTNO,TI.FPRODUCEDATE
,TI.FEXPIRYDATE,TI.FBASEUNITID,TI.FSECUNITID,TI.FCOMBINEID
FROM T_STK_INVBAL TI JOIN T_BD_MATERIAL TM ON TM.FMATERIALID=TI.FMATERIALID
WHERE TI.FSTOCKORGID = 100006
AND TI.FBALTYPE = @BALTYPE --余额类型：初始库存余额1,关账=1
--AND TI.FBALDATE = @BEGINTIME --结存日期
AND CONVERT(VARCHAR(10),TI.FBALDATE,23) = @fclosedate1 --结存日期
--AND TI.FSTOCKID=@STOCKID
) t JOIN T_BD_MATERIAL t1 ON t1.FNUMBER=t.FNUMBER AND t1.FUSEORGID=100006
GROUP BY t1.FMATERIALID,t.FSTOCKID
) m GROUP BY m.fmaterialid,m.FSTOCKID
-- SELECT t1.FMATERIALID,SUM(t.FBASEQTY) fqty
--SELECT 'STK_INVBAL' fformid
--, 1 fqtyaddoption
--, t1.FMATERIALID fmaterialid
----,t2.FSTOCKID
--, SUM(t.FBASEQTY) fbaseqty
--FROM (
-- SELECT TM.FNUMBER
-- ,ISNULL(TI.FBASEENDQTY,0) FBASEQTY
-- ,ISNULL(TI.FSECENDQTY,0) FSECQTY,TI.FMATERIALID FMATERIALID1
-- ,TI.FSTOCKORGID
-- ,TI.FAUXPROPID,TI.FKEEPERID,TI.FKEEPERTYPEID,TI.FLOT,TI.FOWNERID
-- ,TI.FOWNERTYPEID,TI.FSTOCKID,TI.FSTOCKLOCID
-- ,TI.FSTOCKSTATUSID,TI.FBOMID,TI.FMTONO,TI.FPROJECTNO,TI.FPRODUCEDATE
-- ,TI.FEXPIRYDATE,TI.FBASEUNITID,TI.FSECUNITID,TI.FCOMBINEID
-- FROM T_STK_INVBAL TI JOIN T_BD_MATERIAL TM ON TM.FMATERIALID=TI.FMATERIALID
-- WHERE TI.FSTOCKORGID = 100006
-- AND TI.FBALTYPE = 0 --余额类型：关账余额
-- --AND TI.FBALDATE = @BEGINTIME --结存日期
-- AND CONVERT(VARCHAR(10),TI.FBALDATE,23) = '2024-03-31' --结存日期
-- AND TI.FSTOCKID=493513
-- ) t JOIN T_BD_MATERIAL t1 ON t1.FNUMBER=t.FNUMBER AND t1.FUSEORGID=100006
-- GROUP BY t1.FMATERIALID
--SELECT * FROM #m
--SELECT 'LSKC'+CONVERT(VARCHAR(8),@DATE,112)
INSERT INTO XXXX_T_STK_HisInventory
(FID,F_XXXX_Date,FBILLNO,FDOCUMENTSTATUS,F_XXXX_StockOrgId,F_XXXX_StockId
,F_XXXX_MATERIALID,F_XXXX_StockQty,F_XXXX_SysLockQty
,F_XXXX_CreatorId,F_XXXX_CreateDate,F_XXXX_ModifierId,F_XXXX_ModifyDate,F_XXXX_ApproverId,F_XXXX_ApproveDate)
SELECT ROW_NUMBER() OVER ( ORDER BY T.FMATERIALID,T.FSTOCKID )
+(SELECT ISNULL(MAX(FID),0) FROM XXXX_T_STK_HisInventory) AS FID,
@DATE FDate,
CONVERT(
VARCHAR(40),
'LSKC'+CONVERT(VARCHAR(8),@DATE,112)
+CONVERT(VARCHAR(10),T.FSTOCKID)
+ CONVERT(VARCHAR(10), T.FMATERIALID)) FBillNo
,'C'
,T.FSTOCKORGID
,T.FSTOCKID
,T.FMATERIALID
,T.FQty
,0 FSysLockQty
,16394 AS F_XXXX_CreatorId
,GETDATE() F_XXXX_CreateDate
,16394 AS F_XXXX_ModifierId
,GETDATE() F_XXXX_ModifyDate
,16394 AS F_XXXX_ApproverId
,GETDATE() F_XXXX_ApproveDate
FROM (SELECT 100006 AS FSTOCKORGID
,c.FSTOCKID
,c.fqty
,ISNULL(d.FMATERIALID,c.fmaterialid) FMATERIALID
FROM (
SELECT
a.*,b.FNUMBER
FROM #m a LEFT JOIN T_BD_MATERIAL b ON a.fmaterialid=b.FMATERIALID WHERE a.fqty>0
) c LEFT JOIN T_BD_MATERIAL d ON d.FNUMBER=c.FNUMBER AND d.FUSEORGID=1) T
END
GO
View Code
4、创建存储过程2，根据日期范围循环调用存储过程1，生成《历史库存信息》
CREATE PROCEDURE [XXXX_PR_STK_GeneralStockQtyByDate]
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20)
AS
BEGIN
DECLARE @date VARCHAR(50)
DECLARE @bd VARCHAR(50)
DECLARE @ed VARCHAR(50)
DECLARE @i INT
-- 声明一个游标
DECLARE ghCursor CURSOR FOR
--需要处理的结果集 T_PRD_PPBOMENTRY0619
SELECT * FROM date_tables2 WHERE date_column BETWEEN @BEGINTIME AND @ENDTIME
OPEN ghCursor
FETCH NEXT FROM ghCursor INTO @date, @bd,@ed
SET @i=0
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC XXXX_PR_STK_STOCKQTYALLGENERALINIT @date,@bd,@ed
SET @i= @i+1
FETCH NEXT FROM ghCursor INTO @date, @bd,@ed
END
CLOSE ghCursor
DEALLOCATE ghCursor
PRINT '处理行数:'+ CAST(@i AS NVARCHAR(10))
END
GO
View Code
5、执行生成
EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-01-01','2024-01-31' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-02-01','2024-02-29' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-03-01','2024-03-31' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-04-01','2024-04-30' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-05-01','2024-05-31' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-06-01','2024-06-30' EXEC XXXX_PR_STK_GeneralStockQtyByDate '2024-07-01','2024-07-21'
6、查询数据
标签：FNUMBER,星空,批量,金蝶,--,t0,t1,BEGINTIME,ENDTIME From： https://www.cnblogs.com/lanrenka/p/18333206