223 lines
7.4 KiB
Transact-SQL
223 lines
7.4 KiB
Transact-SQL
USE [JSDataMiddlePlatform];
|
||
SET NOCOUNT ON;
|
||
|
||
DECLARE @sku nvarchar(100) = N'CJ019317N1097V01';
|
||
DECLARE @lookback_days int = 30;
|
||
DECLARE @lead_days int = 10;
|
||
DECLARE @has_exact bit = 0;
|
||
DECLARE @start_time datetime2 = DATEADD(day, -@lookback_days, GETDATE());
|
||
DECLARE @end_time datetime2 = GETDATE();
|
||
|
||
IF EXISTS (SELECT 1 FROM dbo.jst_sku WITH (NOLOCK) WHERE sku_id = @sku)
|
||
SET @has_exact = 1;
|
||
|
||
IF OBJECT_ID('tempdb..#matched_sku') IS NOT NULL
|
||
DROP TABLE #matched_sku;
|
||
|
||
SELECT TOP (50)
|
||
s.sku_id,
|
||
s.name AS sku_name,
|
||
s.i_id AS style_code,
|
||
s.other_1 AS spu,
|
||
s.other_7 AS product_no,
|
||
s.cost_price,
|
||
s.other_10 AS board_material,
|
||
s.pic AS preview_pic_url,
|
||
s.modified
|
||
INTO #matched_sku
|
||
FROM dbo.jst_sku AS s WITH (NOLOCK)
|
||
WHERE
|
||
(@has_exact = 1 AND s.sku_id = @sku)
|
||
OR (
|
||
@has_exact = 0
|
||
AND (
|
||
s.sku_id = @sku
|
||
OR s.i_id = @sku
|
||
OR s.other_1 = @sku
|
||
OR s.other_7 = @sku
|
||
OR s.sku_id LIKE '%' + @sku + '%'
|
||
)
|
||
)
|
||
ORDER BY CASE WHEN s.sku_id = @sku THEN 0 ELSE 1 END, s.modified DESC;
|
||
|
||
IF OBJECT_ID('tempdb..#inv_latest') IS NOT NULL
|
||
DROP TABLE #inv_latest;
|
||
|
||
WITH inv_rank AS (
|
||
SELECT
|
||
NULLIF(i.sku_id, '') AS sku_id,
|
||
TRY_CONVERT(decimal(18, 2), i.qty) AS stock_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.order_lock) AS order_lock_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.pick_lock) AS pick_lock_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.lock_qty) AS lock_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.virtual_qty) AS virtual_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.defective_qty) AS defective_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.purchase_qty) AS purchase_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.return_qty) AS return_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.in_qty) AS in_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.min_qty) AS min_qty,
|
||
TRY_CONVERT(decimal(18, 2), i.max_qty) AS max_qty,
|
||
i.wms_co_id,
|
||
i.name AS inventory_note,
|
||
COALESCE(i.updateTime, i.modified, i.createTime) AS inventory_snapshot_time,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY NULLIF(i.sku_id, '')
|
||
ORDER BY COALESCE(i.updateTime, i.modified, i.createTime) DESC, i.Id DESC
|
||
) AS rn
|
||
FROM dbo.jst_inventoryInfo AS i WITH (NOLOCK)
|
||
WHERE NULLIF(i.sku_id, '') IN (SELECT sku_id FROM #matched_sku)
|
||
)
|
||
SELECT
|
||
sku_id,
|
||
stock_qty,
|
||
order_lock_qty,
|
||
pick_lock_qty,
|
||
lock_qty,
|
||
virtual_qty,
|
||
defective_qty,
|
||
purchase_qty,
|
||
return_qty,
|
||
in_qty,
|
||
min_qty,
|
||
max_qty,
|
||
wms_co_id,
|
||
inventory_note,
|
||
inventory_snapshot_time,
|
||
CASE
|
||
WHEN ISNULL(stock_qty, 0) - ISNULL(order_lock_qty, 0) - ISNULL(pick_lock_qty, 0) - ISNULL(lock_qty, 0) < 0
|
||
THEN CAST(0 AS decimal(18, 2))
|
||
ELSE ISNULL(stock_qty, 0) - ISNULL(order_lock_qty, 0) - ISNULL(pick_lock_qty, 0) - ISNULL(lock_qty, 0)
|
||
END AS available_qty
|
||
INTO #inv_latest
|
||
FROM inv_rank
|
||
WHERE rn = 1;
|
||
|
||
IF OBJECT_ID('tempdb..#ship_base') IS NOT NULL
|
||
DROP TABLE #ship_base;
|
||
|
||
SELECT
|
||
NULLIF(a.undefined10, '') AS sku_id,
|
||
NULLIF(a.undefined2, '') AS order_id,
|
||
COALESCE(
|
||
TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 120),
|
||
TRY_CONVERT(datetime2, REPLACE(NULLIF(a.undefined6, ''), '/', '-'), 120),
|
||
TRY_CONVERT(datetime2, NULLIF(a.undefined6, ''), 111),
|
||
TRY_CONVERT(datetime2, a.createdAt)
|
||
) AS ship_dt,
|
||
COALESCE(
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined30, '')),
|
||
TRY_CONVERT(decimal(18, 2), NULLIF(a.undefined20, '')),
|
||
CAST(0 AS decimal(18, 2))
|
||
) AS shipped_qty
|
||
INTO #ship_base
|
||
FROM dbo.jst_goods_profit_kpi_items_afs AS a WITH (NOLOCK)
|
||
WHERE NULLIF(a.undefined10, '') IN (SELECT sku_id FROM #matched_sku)
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%配件%'
|
||
AND ISNULL(NULLIF(a.undefined62, ''), '') NOT LIKE '%特殊单%';
|
||
|
||
IF OBJECT_ID('tempdb..#ship_daily') IS NOT NULL
|
||
DROP TABLE #ship_daily;
|
||
|
||
SELECT
|
||
sb.sku_id,
|
||
CAST(sb.ship_dt AS date) AS ship_date,
|
||
COUNT(DISTINCT sb.order_id) AS order_count,
|
||
SUM(ISNULL(sb.shipped_qty, 0)) AS shipped_qty_sum
|
||
INTO #ship_daily
|
||
FROM #ship_base AS sb
|
||
WHERE sb.ship_dt >= @start_time
|
||
AND sb.ship_dt < @end_time
|
||
GROUP BY sb.sku_id, CAST(sb.ship_dt AS date);
|
||
|
||
IF OBJECT_ID('tempdb..#ship_stats') IS NOT NULL
|
||
DROP TABLE #ship_stats;
|
||
|
||
SELECT
|
||
sd.sku_id,
|
||
SUM(CASE WHEN sd.ship_date >= CAST(DATEADD(day, -7, @end_time) AS date) THEN ISNULL(sd.shipped_qty_sum, 0) ELSE 0 END) AS shipped_qty_7d,
|
||
SUM(CASE WHEN sd.ship_date >= CAST(DATEADD(day, -30, @end_time) AS date) THEN ISNULL(sd.shipped_qty_sum, 0) ELSE 0 END) AS shipped_qty_30d,
|
||
MAX(sd.ship_date) AS last_ship_date
|
||
INTO #ship_stats
|
||
FROM #ship_daily AS sd
|
||
GROUP BY sd.sku_id;
|
||
|
||
-- 1) SKU基础信息 + 库存快照
|
||
SELECT
|
||
ms.sku_id,
|
||
ms.sku_name,
|
||
ms.style_code,
|
||
ms.spu,
|
||
ms.product_no,
|
||
ms.cost_price,
|
||
ms.board_material,
|
||
ms.preview_pic_url,
|
||
inv.stock_qty,
|
||
inv.order_lock_qty,
|
||
inv.pick_lock_qty,
|
||
inv.lock_qty,
|
||
inv.available_qty,
|
||
inv.inventory_snapshot_time,
|
||
inv.wms_co_id
|
||
FROM #matched_sku AS ms
|
||
LEFT JOIN #inv_latest AS inv
|
||
ON inv.sku_id = ms.sku_id
|
||
ORDER BY ms.modified DESC;
|
||
|
||
-- 2) 近N天每日总发货件数
|
||
SELECT
|
||
sd.sku_id,
|
||
ms.sku_name,
|
||
sd.ship_date,
|
||
sd.order_count,
|
||
sd.shipped_qty_sum
|
||
FROM #ship_daily AS sd
|
||
LEFT JOIN #matched_sku AS ms
|
||
ON ms.sku_id = sd.sku_id
|
||
ORDER BY sd.ship_date DESC;
|
||
|
||
-- 3) 缺货预估与补货建议（补货安全线=10天）
|
||
SELECT
|
||
ms.sku_id,
|
||
ms.sku_name,
|
||
inv.available_qty,
|
||
ss.last_ship_date,
|
||
CAST(ISNULL(ss.shipped_qty_7d, 0) / 7.0 AS decimal(18, 2)) AS avg_daily_shipped_7d,
|
||
CAST(ISNULL(ss.shipped_qty_30d, 0) / 30.0 AS decimal(18, 2)) AS avg_daily_shipped_30d,
|
||
CAST(
|
||
CASE
|
||
WHEN ISNULL(ss.shipped_qty_30d, 0) <= 0 THEN NULL
|
||
ELSE inv.available_qty / NULLIF(ISNULL(ss.shipped_qty_30d, 0) / 30.0, 0)
|
||
END AS decimal(18, 2)
|
||
) AS estimated_stockout_days,
|
||
@lead_days AS replenishment_lead_days,
|
||
CAST(
|
||
CASE
|
||
WHEN ISNULL(ss.shipped_qty_30d, 0) <= 0 THEN 0
|
||
WHEN inv.available_qty >= (ISNULL(ss.shipped_qty_30d, 0) / 30.0) * @lead_days THEN 0
|
||
ELSE ((ISNULL(ss.shipped_qty_30d, 0) / 30.0) * @lead_days) - inv.available_qty
|
||
END AS decimal(18, 2)
|
||
) AS suggest_replenish_qty_to_10d,
|
||
CASE
|
||
WHEN inv.sku_id IS NULL THEN '无库存快照'
|
||
WHEN ISNULL(ss.shipped_qty_30d, 0) <= 0 THEN '近30天无发货'
|
||
WHEN inv.available_qty <= 0 THEN '缺货/可用库存<=0'
|
||
WHEN (inv.available_qty / NULLIF(ISNULL(ss.shipped_qty_30d, 0) / 30.0, 0)) <= @lead_days THEN '补货紧急（<=10天）'
|
||
WHEN (inv.available_qty / NULLIF(ISNULL(ss.shipped_qty_30d, 0) / 30.0, 0)) <= (@lead_days + 5) THEN '补货预警（10-15天）'
|
||
ELSE '库存安全'
|
||
END AS stock_risk_level
|
||
FROM #matched_sku AS ms
|
||
LEFT JOIN #inv_latest AS inv
|
||
ON inv.sku_id = ms.sku_id
|
||
LEFT JOIN #ship_stats AS ss
|
||
ON ss.sku_id = ms.sku_id
|
||
ORDER BY
|
||
CASE
|
||
WHEN inv.sku_id IS NULL THEN 9
|
||
WHEN ISNULL(ss.shipped_qty_30d, 0) <= 0 THEN 8
|
||
WHEN inv.available_qty <= 0 THEN 1
|
||
WHEN (inv.available_qty / NULLIF(ISNULL(ss.shipped_qty_30d, 0) / 30.0, 0)) <= @lead_days THEN 2
|
||
WHEN (inv.available_qty / NULLIF(ISNULL(ss.shipped_qty_30d, 0) / 30.0, 0)) <= (@lead_days + 5) THEN 3
|
||
ELSE 4
|
||
END,
|
||
estimated_stockout_days ASC;
|