T-SQL查询计算缺货-Java 学习之路

最终目标是计算新产品的调整后每日成交量 .

首先，我需要知道什么时候物品缺货，这就是杀死我的原因 .

TransaDate  ItemCode    TransQty    OnHandBefore    OnHandAfter
1/1/2016    xyz-125       -5              20               15
1/4/2016    xyz-126       -4              15               11
1/7/2016    xyz-127       -2              11                9
1/8/2016    xyz-128       -6               9                3
1/8/2016    xyz-129       -3               3                0
1/13/2016   xyz-130       20               0               20
1/13/2016   xyz-131       -4              20               16
1/13/2016   xyz-132      -10              16                6
1/18/2016   xyz-133       -6               6                0
1/23/2016   xyz-134        5               0                5
1/26/2016   xyz-135       -5               5                0
1/28/2016   xyz-136       20               0               20
1/28/2016   xyz-137       -4              20               16

任何时候OnHandAfter为零我想要整天考虑缺货的项目 . 理想情况下，我会忽略那些有0或之前的日子，因为这些都是部分销售 . 但对我来说，重要且困难的部分是物品缺货后的日子 . 没有条目所以我不能直接加入 . 所以在上面的例子中，该项目在26日和27日缺货，即使没有列出第27项 . 总共这个例子显示1月8,9,10,11,12,13缺货，然后我们收到20成股票，然后1月18,19,20,21,22,23,26,27,28也是缺货 .

已售总数：49
平均每日交易量：49/31 = 1.58
停售天数：15
调整后的每日交易量：49 /（31-15）= 3.06

由于没有记录是没有销售的日子我认为我需要创建一个包含该范围内所有日期的派生表（或类似的东西） . 为了使其更复杂，每个项目的日期范围将是动态的，我需要知道在我们的网站上项目上线的前30天内销售的数量 . 因此，每个项目的开始日期必须是动态的 .

另一个主表是项目表 . 我会展示到目前为止我所拥有的东西，但它会得到所有帮助 . 我完全失去了构建这个的最好方法 .

这是我最好的，但遗憾的是不完整和抱歉的尝试，展示我想要的东西以及我认为我可能会如何实现 .

With CTE_FirstSaleDate as 
(
 SELECT MIN(TransDate) as FirstSoldOn FROM InventoryTransaction
), 
WITH CTE_AllDatesTable
AS
(
  SELECT FirstSoldOn AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) < DATEADD(dd, 30, FirstSoldOn)
),
CTE_OOS as
(
SELECT TOP 1 i.ItemCode, it.TransactionDate
FROM InventoryTransaction it
WHERE ( it.OnHandAfter = 0 )
ORDER BY it.TransactionDate DESC
)
Select i.ItemCode, DaysCount, DaysOOSCount, AvgDlyVol, AdjDlyVol 
From Items i
Join InventoryTransaction...

关于我如何获得一堆表，动态日期不在表中以及所有这些其他内容的任何帮助将非常感激 . 当然，如果有一个神奇的脚本会很棒，但每个月会运行一次，所以打破它很好 .

1 回答

试试这个以获得缺货天数：

;WITH CTE
AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY TransaDate) as RN
    FROM InventoryTransaction
)
SELECT c1.TransaDate, c2.TransaDate, DATEDIFF(d, c1.TransaDate, c2.TransaDate) As NumDays
FROM CTE C1
INNER JOIN CTE C2
    ON C1.RN = C2.RN - 1
WHERE C1.OnHandAfter = 0 AND C2.OnHAndBefore = 0

编辑

如果我假设ItemCode是链接特定项目的所有事务的事物列（这从您的示例中看起来不正确）那么这将查询将工作：

;WITH CTE
AS
(
    SELECT *
           ,ROW_NUMBER() OVER (Partition By ItemCode ORDER BY TransaDate) as RN
    FROM InventoryTransaction
)
SELECT c1.TransaDate, c2.TransaDate, 
       DATEDIFF(d, c1.TransaDate, COALESCE(c2.TransaDate, c1.TransaDate)) As NumDays
FROM CTE C1
LEFT JOIN CTE C2
    ON C1.RN = C2.RN - 1 AND C1.ItemCode = C2.ItemCode
WHERE C1.OnHandAfter = 0 AND C2.OnHAndBefore = 0

回复于 2024-05-06T05:34:13+08:00

T-SQL查询计算缺货

1 回答

相关问题