成品库存周转率报表（二）

 成品库存周转率报表（二）

一、报表逻辑

组织=供应链中心，仓库=成品仓，区间可选，可单独搜索某物料,物料基础数据为使用组织=供应链中心，仓库=成品仓作为底表
（1）库存数取自《库存账龄分析表》或《库存汇总查询》-历史查询
（2）期初库存为起始日期的查询库存 期末库存为结束日期的查询库存
（3）平均库存数量=（期初库存数量+期末库存数量）/2
（4）生产入库数量为选定日期范围内生产入库单的数量（按审核日期）
（5）销售出库数量为选定日期范围内销售出库单数量（按审核日期）
（6）产品库存周转率=360/（结束日期-起始日期）*（销售出库数量/平均库存数量）
（7）产品库存周转天数=360/库存周转率

 

二、报表设计

只有存储过程不同，直接替换即可。

 

根据日期返回当天的库存

CREATE PROCEDURE XXXX_PR_STK_STOCKQTY(
@STOCKID INT,
@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)
IF(@BEGINTIME='2024-01-01')
BEGIN
SET @fclosedate1=@BEGINTIME
SET @BALTYPE=1
END
--PRINT '@fclosedate1:'+@fclosedate1


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,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=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
--,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 = @BALTYPE --余额类型：关账余额=0，初始库存余额=1
     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
) m GROUP BY m.fmaterialid

 

 SELECT * FROM #m

  
END
GO

根据区间计算成品仓物料的库存周转率

CREATE PROCEDURE XXXX_PR_STK_ITO(
@STOCKID INT,
@BEGINTIME VARCHAR(20),
@ENDTIME VARCHAR(20),
@t VARCHAR(100)
)
AS
BEGIN
--传入开始时间6.2  ，结束时间7.23
--DECLARE @BEGINTIME VARCHAR(20)
--SET @BEGINTIME='2024-06-02 00:00:00'
--DECLARE @ENDTIME VARCHAR(20)
--SET @ENDTIME='2024-07-23 00:00:00'
--DECLARE @STOCKID INT
--SET @STOCKID=493513



SET @BEGINTIME=@BEGINTIME+' 00:00:00'
SET @ENDTIME=@ENDTIME+'  00:00:00'

--获取当前关账最后日期2024-05-31 00:00:00.000  【开始时间】 sqlserver转短日期
DECLARE @fclosedate1 VARCHAR(10);
SET @fclosedate1=ISNULL((SELECT CONVERT(VARCHAR(10), DATEADD(D,1,MAX(FCLOSEDATE)),23) fclosedate 
FROM T_STK_CLOSEPROFILE WHERE ((FORGID = (100006) AND FCATEGORY = 'STK') 
AND (FCLOSEDATE < @BEGINTIME)) GROUP BY FORGID),'2024-01-01')

DECLARE @BEGINTIME1 VARCHAR(10);
SET @BEGINTIME1=(
SELECT 
    CONVERT(VARCHAR(10),DATEADD(D,1,TRY_CAST(@BEGINTIME as datetime)),23)
)

declare
  @tab1 table
  (
   fmaterialid   bigint,
   fqty bigint
  )

  --SELECT CONVERT(VARCHAR(10),GETDATE(),23)
  ----日期+1天
  --SELECT DATEADD(D,1,GETDATE())

  --  SELECT DATEADD(D,1,NULL)

  INSERT @tab1 EXEC   XXXX_PR_STK_STOCKQTY @STOCKID,@fclosedate1,@BEGINTIME1;

--获取当前关账最后日期  【结束时间】 2024-06-30 00:00:00.000
DECLARE @fclosedate2 VARCHAR(10);
SET @fclosedate2=ISNULL((SELECT CONVERT(VARCHAR(10), DATEADD(D,1,MAX(FCLOSEDATE)),23) fclosedate 
FROM T_STK_CLOSEPROFILE WHERE ((FORGID = (100006) AND FCATEGORY = 'STK') 
AND (FCLOSEDATE < @ENDTIME)) GROUP BY FORGID),'2024-01-01')



DECLARE @ENDTIME1 VARCHAR(10);
SET @ENDTIME1=(
SELECT 
    CONVERT(VARCHAR(10),DATEADD(D,1,TRY_CAST(@ENDTIME as datetime)),23)
)

declare
  @tab2 table
  (
   fmaterialid   bigint,
   fqty bigint
  );

INSERT @tab2 EXEC   XXXX_PR_STK_STOCKQTY @STOCKID,@fclosedate2,@ENDTIME1;
 

--期间销售出库数
SELECT  
  t2.FMATERIALID fmaterialid
--,t2.FSTOCKID
, SUM( t2.FBASEUNITQTY) fqty
INTO #tab3
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.FAPPROVEDATE >= @BEGINTIME) AND (t0.FAPPROVEDATE < @ENDTIME1)) 
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'))
) GROUP BY t2.FMATERIALID


--期间产成品入库
SELECT   t1.FMATERIALID fmaterialid
--, t1.FSTOCKID fstockid
, SUM(t1.FBASEREALQTY) fqty
INTO #tab4
FROM T_PRD_INSTOCK t0 
LEFT OUTER JOIN T_PRD_INSTOCKENTRY t1 ON t0.FID = t1.FID 
LEFT JOIN T_PRD_MO t2 ON t2.fid=t1.FMoId
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L t3 ON t3.FENTRYID=t2.F_XXXX_WKTYPE
LEFT JOIN dbo.T_BAS_BILLTYPE_L t4 ON t4.FBILLTYPEID=t2.FBILLTYPE
--LEFT OUTER JOIN t_BD_Stock st17 ON t1.FSTOCKID = st17.FStockId 
WHERE (((((((t0.FAPPROVEDATE >= @BEGINTIME) AND (t0.FAPPROVEDATE < @ENDTIME1)) 
AND t0.FSTOCKORGID = 100006) AND t0.FENTRUSTINSTOCKID = 0) 
AND t0.FFORMID = 'PRD_INSTOCK') AND t0.FCANCELSTATUS = 'A')
AND t1.FSTOCKID =@STOCKID
AND t4.FNAME='汇报入库-普通生产'
AND t3.FDATAVALUE IN ('普通生产订单','研发试制订单','销售改机订单','PCBA加工订单')
--AND ((st17.FNUMBER >= '02') AND (st17.FNUMBER <= '02'))
)  GROUP BY t1.FMATERIALID

 

--sqlserver 计算两个日期之间的天数


--SELECT ROUND(isnull(360.0/DATEDIFF(DAY,'2024-06-02','2024-07-23'),0),2)
--SELECT DATEDIFF(DAY,'2024-06-02','2024-07-23')

--SELECT 360.0/51  --7.058823

--sqlserver 声明小数
DECLARE @daterate NUMERIC(10,2)
SET @daterate=(SELECT ROUND(isnull(360.0/DATEDIFF(DAY,@BEGINTIME,@ENDTIME),0),2))

SELECT ROW_NUMBER() OVER ( ORDER BY m2.fmaterialid ) FID
,493513 FSTOCKID
,'02' AS FSTOCKNUMBER
,'成品仓' AS FSTOCKNAME
, m2.* 
,CASE WHEN m2.fito>0 THEN CAST(ROUND(360/m2.fito,0) AS INT) ELSE 0 END FITD
INTO #t_ylm
FROM (
SELECT m11.* 
,CASE WHEN m11.favgqty>0 THEN CAST(ROUND(@daterate*(m11.foutqty/m11.favgqty),2) AS DECIMAL(10,2)) ELSE 0 END FITO
FROM (
    SELECT m1.*
    ,CAST(ROUND((m1.fqcqty+m1.fqmqty)/2.0,2) AS DECIMAL(10,2)) FAVGQTY
    --,ROUND(@daterate*(m1.foutqty/ROUND((m1.fqcqty+m1.fqmqty)/2.0,2)),2) fito
    FROM (
        SELECT m.*
        , CAST(ISNULL(a.fqty,0) AS INT) FQCQTY
        , CAST(ISNULL(b.fqty,0) AS INT) FQMQTY 
        , CAST(ISNULL(c.fqty,0) AS INT) FOUTQTY
        , CAST(ISNULL(d.fqty,0) AS INT) FINQTY
        FROM (
            SELECT a1.FMATERIALID
            ,a1.FNUMBER FMATERIALNUMBER
            ,f1.FNAME FMATERIALNAME
            ,f1.FSPECIFICATION
            ,a1.FUSEORGID 
            ,d1.FBASEUNITID
            ,e1.FNAME FBASEUNITNAME
            FROM T_BD_MATERIAL a1 
            JOIN t_BD_MaterialStock b1 ON a1.FMATERIALID=b1.FMATERIALID
            JOIN T_BD_STOCK_L  c1 ON c1.FSTOCKID=b1.FSTOCKID
            JOIN t_BD_MaterialBase d1 ON d1.FMATERIALID=a1.FMATERIALID
            LEFT JOIN T_BD_UNIT_L e1 ON e1.FUNITID=d1.FBASEUNITID
            LEFT JOIN T_BD_MATERIAL_L f1 ON f1.FMATERIALID=a1.FMATERIALID
            WHERE c1.FNAME='成品仓' AND a1.FUSEORGID=100006
            --合并多个表的物料
                --SELECT fmaterialid FROM @tab1
                --union
                --SELECT fmaterialid FROM @tab2
                --union
                --SELECT fmaterialid FROM #tab3
        ) m 
        LEFT JOIN @tab1 a ON m.fmaterialid=a.fmaterialid
        LEFT JOIN @tab2 b ON m.fmaterialid=b.fmaterialid
        LEFT JOIN #tab3 c ON m.fmaterialid=c.fmaterialid
        LEFT JOIN #tab4 d ON m.fmaterialid=d.fmaterialid
    ) m1
    ) m11
) m2
 


 --最后输出到临时表
DECLARE @cmdtext VARCHAR(MAX);

BEGIN
SET
    @cmdtext = 'SELECT  *' + ' into ' + @t + ' FROM #t_ylm';

END;

EXEC(@cmdtext);


END
GO

 

 数据源插件

 

using Kingdee.BOS;
using Kingdee.BOS.App;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using Krystal.K3Cloud.Core.Const;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;


namespace Krystal.K3.App.Report.SAL
{
    /// <summary>
    /// 功能描述    ：成品库存周转率查询-报表插件  
    /// 创 建 者    ：Administrator
    /// 创建日期    ：2024/7/31 15:52:57 
    /// 最后修改者  ：Krystal
    /// 最后修改日期：2024/7/31 15:52:57 
    /// </summary>
    [Description("成品库存周转率查询-报表插件"), HotUpdate]
    public class STK_ITORpt: SysReportBaseService
    {

        #region <变量>
        /// <summary>
        /// 汇总字段
        /// </summary>
        List<string> listSumColumn = new List<string>() { "FQCQTY", "FQMQTY", "FOUTQTY", "FINQTY", "FAVGQTY" };

        /// <summary>
        /// 用于sql的取数
        /// </summary>
        protected List<string> lstSql = new List<string>();

        /// <summary>
        /// 高级过滤条件
        /// </summary>
        private string _sWhereFilter = string.Empty;

        /// <summary>
        ///  排序条件
        /// </summary>
        private string _sOrderBy = string.Empty;

        /// <summary>
        ///  分组条件
        /// </summary>
        private string _sGroupFilter = string.Empty;

        /// <summary>
        /// 页面过滤参数
        /// </summary>
        FilterArgs _filterArgs = new FilterArgs();

        /// <summary>
        /// 临时表名
        /// </summary>
        protected string temp_detail;
        #endregion <变量>

        #region <属性>
        #region 过滤参数 FilterArgs
        internal class FilterArgs
        {
            private DateTime _beginTime;
            private DateTime _endTime;
            //物料
            private string _FMaterial = string.Empty;
            public DateTime BeginTime
            {
                get { return this._beginTime; }
                set { this._beginTime = value; }
            }
            public DateTime EndTime
            {
                get { return this._endTime; }
                set { this._endTime = value; }
            }

            public string FMaterial
            {
                get { return this._FMaterial; }
                set { this._FMaterial = value; }
            }

        }
        #endregion
        #endregion <属性>

        #region <构造方法和析构方法>
        #endregion <构造方法和析构方法>

        #region <方法>
        //界面加载表头
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("FSTOCKNUMBER", new LocaleValue("仓库编码", this.Context.DefaultLocale.LCID));
            header.AddChild("FSTOCKNAME", new LocaleValue("仓库名称", this.Context.DefaultLocale.LCID));
            header.AddChild("FMATERIALNUMBER", new LocaleValue("物料编码", this.Context.DefaultLocale.LCID));
            header.AddChild("FMATERIALNAME", new LocaleValue("物料名称", this.Context.DefaultLocale.LCID));
            header.AddChild("FSPECIFICATION", new LocaleValue("规格型号", this.Context.DefaultLocale.LCID));
            header.AddChild("FBASEUNITNAME", new LocaleValue("基本单位", this.Context.DefaultLocale.LCID));
            header.AddChild("FQCQTY", new LocaleValue("期初结存数量", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
            header.AddChild("FQMQTY", new LocaleValue("期末结存数量", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
            header.AddChild("FINQTY", new LocaleValue("生产入库数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
            header.AddChild("FAVGQTY", new LocaleValue("平均库存数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FOUTQTY", new LocaleValue("销售出库数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);
            header.AddChild("FITO", new LocaleValue("库存周转率", this.Context.DefaultLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FITD", new LocaleValue("库存周转天数", this.Context.DefaultLocale.LCID), SqlStorageType.SqlInt);

            //header.AddChild("F_XXXX_OrgName", new LocaleValue("供应组织", this.Context.DefaultLocale.LCID));

            return header;
        }


        /// <summary>
        /// 获取过滤条件
        /// </summary>
        /// <param name="filter"></param>
        private DynamicObject GetFilter(IRptParams filter)
        {
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            _filterArgs.FMaterial = GetBaseDataByKey(dyFilter, "F_XXXX_MaterialId", "Number");
            _filterArgs.BeginTime = GetDataByKey(dyFilter, "F_XXXX_BeginDate") == string.Empty ? DateTime.MinValue : Convert.ToDateTime(XXXXCommonUtil.GetDataByKey(dyFilter, "F_XXXX_BeginDate"));
            _filterArgs.EndTime = GetDataByKey(dyFilter, "F_XXXX_EndDate") == string.Empty ? DateTime.MaxValue : Convert.ToDateTime(XXXXCommonUtil.GetDataByKey(dyFilter, "F_XXXX_EndDate"));
            _sWhereFilter = filter.FilterParameter.FilterString;
            _sGroupFilter = filter.FilterParameter.GroupbyString;
            _sOrderBy = filter.FilterParameter.SortString;
            return dyFilter;
        }

        /// <summary>
        /// 构造报表表头标题
        /// </summary>
        /// <param name="filter">过滤条件对象</param>
        /// <returns></returns>
        private ReportTitles BuildTitle(IRptParams filter)
        {
            ReportTitles reportTitle = new ReportTitles();
            //物料
            string materNumber = string.IsNullOrWhiteSpace(this._filterArgs.FMaterial) ? "全部" : this._filterArgs.FMaterial;
            reportTitle.AddTitle("F_XXXX_MaterialNumberTitle", materNumber);
            string sDateFrom = _filterArgs.BeginTime == null ? string.Empty : _filterArgs.BeginTime.ToShortDateString().ToString();
            string sDateTo = _filterArgs.EndTime == null ? string.Empty : _filterArgs.EndTime.ToShortDateString().ToString();
            string ds = string.Format("{0}{1}{2}", sDateFrom, Kingdee.BOS.Resource.ResManager.LoadKDString(" 至 ", "004102030003172", Kingdee.BOS.Resource.SubSystemType.SCM), sDateTo);
            //reportTitle.AddTitle("F_XXXX_DateRangeTitle", ds);
            string beginApplicationMonthStr = _filterArgs.BeginTime.ToString("yyyy/MM/dd");//2024/06/01 至 2024/06/30
            string endApplicationMonthStr = _filterArgs.EndTime.ToString("yyyy/MM/dd");
            reportTitle.AddTitle("F_XXXX_DateRangeTitle", string.Format("{0}{1}{2}"
               , beginApplicationMonthStr
               , Kingdee.BOS.Resource.ResManager.LoadKDString(" 至 ", "004102030003172", Kingdee.BOS.Resource.SubSystemType.SCM)
               , endApplicationMonthStr));

            return reportTitle;
        }
        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            return BuildTitle(filter);
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            //base.BuilderReportSqlAndTempTable(filter, tableName);
            //构造过滤条件
            DynamicObject dyFilter = this.GetFilter(filter);
            GetReport();
            if (filter.FilterParameter.SummaryRows.Count > 0)
            {
                List<string> outColumn = filter.FilterParameter.ColumnInfo.Select(s => s.FieldName).ToList();
                List<string> summaryColumn = filter.FilterParameter.SummaryRows.Select(s => s.FieldName).ToList();
                outColumn.RemoveAll(s => this.listSumColumn.Contains(s));
                outColumn.RemoveAll(s => summaryColumn.Contains(s));
                filter.FilterParameter.ColumnInfo.RemoveAll(s => outColumn.Contains(s.FieldName));
            }

            string sbSql = string.Empty;
            this.KSQL_SEQ = string.Format(this.KSQL_SEQ, this._sGroupFilter.Length > 0 ? this._sGroupFilter : this._sOrderBy.Length > 0 ? this._sOrderBy : "FID");
            //string filterColumn = string.Empty;
            string filterColumn = string.Join(",", filter.FilterParameter.ColumnInfo.Select(s => (this.listSumColumn.Contains(s.FieldName) && this._sGroupFilter.Length > 0) ? "sum(" + s.FieldName + ") as " + s.FieldName : s.FieldName).ToList());
            if (string.IsNullOrEmpty(filterColumn) || string.IsNullOrWhiteSpace(filterColumn))
            {
                filterColumn += "FID";
            }
            sbSql = string.Format(@"select {1},{0} from {2} T99", filterColumn, this.KSQL_SEQ, this.temp_detail);
            this.ConstructFastFilter(ref sbSql);
            if (this._sGroupFilter.Length > 0) sbSql += " group by " + this._sGroupFilter;
            sbSql = string.Format(@"{0}select * into {1} from ({2}) report", OtherConst.DIALECT, tableName, sbSql);
            DBUtils.Execute(this.Context, sbSql);
            string[] str = { this.temp_detail };
            DeleteTemporaryTableNames(this.Context, str);
        }
        private void ConstructFastFilter(ref string sbSql)
        {
            sbSql += " WHERE 1=1 " + ((this._sWhereFilter.Length > 0) ? " and " + this._sWhereFilter : "");
            if (!string.IsNullOrWhiteSpace(this._filterArgs.FMaterial))
                sbSql += string.Format(@" AND FMATERIALNUMBER='{0}'", this._filterArgs.FMaterial);  //物料

        }
        private void GetReport()
        {
            this.temp_detail = CreateTemporaryTableNames(base.Context, 1)[0];
            List<SqlParam> para = new List<SqlParam>()
            {
                new SqlParam("@STOCKID", KDDbType.Int32,493513),
                new SqlParam("@BEGINTIME", KDDbType.String,_filterArgs.BeginTime.ToString("yyyy-MM-dd")),
                new SqlParam("@ENDTIME",KDDbType.String,_filterArgs.EndTime.ToString("yyyy-MM-dd")),
                new SqlParam("@t",KDDbType.String,temp_detail)
            };
            DBUtils.ExecuteDataSet(this.Context, System.Data.CommandType.StoredProcedure, string.Format(@"{0}XXXX_PR_STK_ITO", OtherConst.DIALECT), para);//XXXX_PR_STK_ITORPT--获取历史库存信息 XXXX_PR_STK_ITO--查询日期库存
        }
        private static string[] CreateTemporaryTableNames(Context context, int count)
        {
            return ServiceHelper.GetService<IDBService>().CreateTemporaryTableName(context, count);
        }


        /// <summary>
        /// 标识需要删除的临时表
        /// </summary>
        /// <param name="context"></param>
        /// <param name="temptableNames"></param>
        /// <returns></returns>
        private static bool DeleteTemporaryTableNames(Context context, string[] temptableNames)
        {
            return ServiceHelper.GetService<IDBService>().DeleteTemporaryTableName(context, temptableNames);
        }


        /// <summary>
        /// 获取DynamicObject数据包中指定key的值
        /// </summary>
        private static string GetDataByKey(DynamicObject doFilter, string sKey)
        {
            string sReturnValue = string.Empty;
            if (doFilter != null && doFilter[sKey] != null && !string.IsNullOrWhiteSpace(Convert.ToString(doFilter[sKey])))
            {
                sReturnValue = Convert.ToString(doFilter[sKey]);
            }
            return sReturnValue;
        }

        /// <summary>
        /// 获取DynamicObject数据包中指定key的基础资料的指定属性的值
        /// </summary>
        private static string GetBaseDataByKey(DynamicObject doFilter, string sKey, string sItem)
        {
            string sReturnValue = string.Empty;
            if (doFilter != null && doFilter[sKey] != null && !string.IsNullOrWhiteSpace(Convert.ToString(((DynamicObject)doFilter[sKey])[sItem])))
            {
                DynamicObject doTemp = doFilter[sKey] as DynamicObject;
                sReturnValue = Convert.ToString(doTemp[sItem]);
            }
            return sReturnValue;
        }

        #endregion <方法>

    }
}