时间戳

首页 > 解决方案 > 循环依赖地狱。在当前行中使用上一行的值

sql-server - 循环依赖地狱。在当前行中使用上一行的值

问题描述

我想计算物料清单并陷入某些属性计算。

这是包含所有数据和所需公式的 Google 表格链接

我手头有
数量 (@QOH) - 95
安全库存 (@SS) - 58

DECLARE @qoh INT = 95
      , @ss  INT = 58
      , @eoq INT = 96;

CREATE TABLE #data
(
     PartId INT
  ,  SalesOrderQty INT
  , SalesDate     DATE
);

INSERT INTO #data (PartId
                 , SalesOrderQty
                 , SalesDate)
VALUES (1, 75, '20190101')
     , (1, 100, '20190201')
     , (1, 115, '20190301')
     , (1, 95, '20190401')
     , (1, 132, '20190501');

我需要计算 3 个实体：

预期数据

|--------|----------|-------|---------------|-----------------|
| PartId | Date     |Supply | EOQMultiplier | RecommendedQty  |
|--------|----------|-------|---------------|-----------------|
| 1      | 20190101 | -38   | 1             | 96              |
| 1      | 20190201 | -4    | 1             | 96              |
| 1      | 20190301 | -19   | 1             | 96              |
| 1      | 20190401 | 1     | 1             | 1               |
| 1      | 20190501 | -131  | 2             | 192             |
|--------|----------|-------|---------------|-----------------|

我面临的问题是@Supply并且@RecommendedQty正在相互引用，我的解决方案已经变得过于复杂和hacky，因为SQL Server 2008 R2不支持一些有用的WINDOW函数

标签： sql-serversql-server-2008sql-server-2008-r2

解决方案

这是我推荐基于游标的解决方案的少数情况之一。它不能很好地扩展，所以如果你的数据很大，这是一个糟糕的选择，但至少它会以一种相当容易理解的方式完成你需要的事情

DECLARE @qoh INT = 95
      , @ss  INT = 58
      , @eoq INT = 96;

CREATE TABLE #data
(
     PartId INT
  ,  SalesOrderQty INT
  , SalesDate     DATE
);

INSERT INTO #data (PartId
                 , SalesOrderQty
                 , SalesDate)
VALUES (1, 75, '20190101')
     , (1, 100, '20190201')
     , (1, 115, '20190301')
     , (1, 95, '20190401')
     , (1, 132, '20190501');

;with cteOrdered as (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY  SalesDate) as RowNum 
    FROM #data 
)
SELECT *, @QOH-@SS-SalesOrderQty as Supply, 1 as EOQMultiplier, @QOH as RecommendedQty
INTO #DataTemp 
FROM cteOrdered

DECLARE @PartId INT
DECLARE  @SalesOrderQty int
DECLARE  @SalesDate DATE 
DECLARE  @RowNum int
DECLARE  @Supply int
DECLARE  @EOQMultiplier int
DECLARE  @RecommendedQty int
DECLARE  @PrevRecQty int

DECLARE curMonth CURSOR FOR   
SELECT PartId , SalesOrderQty , SalesDate , RowNum , Supply , EOQMultiplier, RecommendedQty 
FROM #DataTemp
ORDER BY PartID, RowNum;

OPEN curMonth  

FETCH NEXT FROM curMonth   
INTO @PartId , @SalesOrderQty , @SalesDate , @RowNum , @Supply , @EOQMultiplier, @RecommendedQty

WHILE @@FETCH_STATUS = 0  
BEGIN  
    IF (@RowNum = 1) BEGIN --No prior month
        SET @Supply = @QOH - @SS - @SalesOrderQty
    END ELSE BEGIN
        SET @Supply = @PrevRecQty - @SalesOrderQty 
    END
    SET @EOQMultiplier = CEILING(ABS(@Supply*1.0/@eoq))
    if (@Supply <= 0) BEGIN
        SET @RecommendedQty = @EOQMultiplier * @eoq 
    END ELSE BEGIN 
        SET @RecommendedQty = @Supply
    END

    SET @PrevRecQty = @RecommendedQty

    UPDATE #DataTemp 
    SET Supply = @Supply, EOQMultiplier = @EOQMultiplier, RecommendedQty= @RecommendedQty
    WHERE PartId = @PartId AND RowNum = @RowNum

    FETCH NEXT FROM curMonth   
    INTO @PartId , @SalesOrderQty , @SalesDate , @RowNum , @Supply , @EOQMultiplier, @RecommendedQty
END   
CLOSE curMonth;  
DEALLOCATE curMonth; 

SELECT * FROM #DataTemp 

DROP TABLE #data 
DROP TABLE #DataTemp

推荐阅读