--历史库存的计算方式
SELECT
CONCAT(
`WarehouseName`,
`WarehouseCode`
) AS '仓库名称',
`WareFieldCode` AS '库区',
`WarePositionCode` AS '库位',
SUM(
CASE
WHEN `OrderType` = 1 THEN `Qty`
ELSE `Qty`
END
) AS '库存数量',
SUM(
CASE
WHEN `OrderType` = 1 THEN `CostAmount`
ELSE `CostAmount`
END
) AS '库存成本金额',
`SkuCode` AS '商品编码',
`SkuName` AS '商品名称',
`Barcode` AS '商品条码',
CONCAT(
ROUND(`NUnit1`, 0),
`Unit`,
'/',
`Unit1`
) AS '件规格'
FROM b2b_wms.st_warestoreflow
WHERE
`TenantId` IN (
'3286299645965762560',
'3131897506372599808'
)
AND `WarehouseId` IN (
'3370028676427628544',
'3353714265942151168'
)
AND `CompletionTime` <= @oStartTime
AND `CompletionTime` <= @oEndTime
AND `SkuCode` = @SkuCode
--
WITH
-- CTE1: 当前有效库存及其完整属性
CurrentInventory AS (
SELECT DISTINCT
c.`Name` AS hName,
c.`Code` AS hCode,
e.`X4`,
e.`Code` AS fCode,
d.`Code` AS pCode,
f.`SkuCode`,
f.`SkuName`,
f.`Barcode`,
f.`Unit`,
f.`Subtitle`,
b.`Qty`,
b.`Amount`,
a.`FnType`,
a.`WaresId`,
b.`SupplierId`
FROM
b2b_wms.st_warestore a
JOIN b2b_wms.st_warestoreitem b ON a.`Id` = b.`WareStoreId`
JOIN b2b_wms.st_warehouse c ON a.`WarehouseId` = c.`Id`
JOIN b2b_wms.st_wareposition d ON b.`WarePositionId` = d.`Id`
JOIN b2b_wms.st_warefield e ON d.`WareFieldId` = e.`Id`
AND d.`WarehouseId` = e.`WarehouseId`
JOIN b2b_wms.st_wares f ON a.`WaresId` = f.`Id`
WHERE
a.`TenantId` IN ('3286299645965762560', '3131897506372599808')
AND a.`WarehouseId` IN ('3370028676427628544', '3353714265942151168')
AND b.`Qty` > 0
),
-- CTE2: 出库订单汇总（KPI 数据）
OutboundKPI AS (
SELECT
b.`WaresId`,
COUNT(DISTINCT a.`Id`) AS 出库频次,
SUM(b.`Qty`) AS 总出库数量,
SUM(b.`F9`) AS 出库总金额
FROM
b2b_wms.st_outstockorder a
JOIN b2b_wms.st_outstockorderitem b ON a.`Id` = b.`OutStockOrderId`
WHERE
a.`TenantId` IN ('3286299645965762560', '3131897506372599808')
AND a.`WarehouseId` IN ('3370028676427628544', '3353714265942151168')
AND a.`Status` = 3
AND a.`CompletionTime` >= @oStartTime
AND a.`CompletionTime` <= @oEndTime
GROUP BY
b.`WaresId`
)
-- 主查询：组合库存与出库 KPI，计算各项指标
SELECT
CONCAT(t.hName, t.hCode) AS '仓库名称',
CASE
WHEN t.X4 = 0 THEN CONCAT('分拣区', t.fCode)
WHEN t.X4 = 1 THEN CONCAT('储货区', t.fCode)
WHEN t.X4 = 2 THEN CONCAT('隔离区', t.fCode)
ELSE '其他'
END AS '库区',
t.pCode AS '库位',
t.SkuCode AS '商品编码',
t.SkuName AS '商品名称',
t.Barcode AS '商品条码',
t.Unit AS '单位',
SUBSTRING(t.Subtitle, 1, 5) AS '规格',
t.Qty AS '库存数量',
t.Amount AS '库存金额',
CASE
WHEN t.FnType = 3 THEN '拆零'
WHEN t.FnType = 1 THEN '整件'
ELSE '其他'
END AS '分拣类型',
-- 出库统计
COALESCE(kpi.出库频次, 0) AS '出库频次（单数）',
ROUND(
COALESCE(kpi.出库频次, 0) / GREATEST(DATEDIFF(@oEndTime, @oStartTime), 1),
4
) AS '出库频率（次/天）',
COALESCE(kpi.总出库数量, 0) AS '总出库数量',
COALESCE(kpi.出库总金额, 0.00) AS '出库总金额',
-- 平均每单出库量
CASE
WHEN kpi.出库频次 > 0 THEN ROUND(kpi.总出库数量 / kpi.出库频次, 2)
ELSE 0.00
END AS '平均出库单量',
-- 库存周转率（近似）= 出库金额 / 当前库存金额（简化版）
CASE
WHEN t.Amount > 0 THEN ROUND(COALESCE(kpi.出库总金额, 0) / t.Amount, 2)
ELSE NULL
END AS '库存周转率（近似）',
-- 周转天数 = (当前库存金额 / 日均出库金额)
CASE
WHEN kpi.出库总金额 > 0 THEN
ROUND((t.Amount / kpi.出库总金额) * DATEDIFF(@oEndTime, @oStartTime), 2)
ELSE NULL
END AS '库存周转天数（周期内）'
FROM CurrentInventory t
LEFT JOIN OutboundKPI kpi ON t.WaresId = kpi.WaresId
WHERE t.Qty > 0 -- 确保有库存
ORDER BY kpi.出库总金额 DESC, t.SkuCode;
提高数据准确性
最新发布
扫一扫
1128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
