时间戳

首页 > 解决方案 > 2个数据集之间的项目结束总数

sql - 2个数据集之间的项目结束总数

问题描述

我有一个查询，按生产订单 (MR #) 提取原材料的组件使用情况。下面我将在 8 月 2 日使用 514 磅的物品 IM02811。还没有为 MR56333 挑选或分配要消耗的批次（我们必须根据到期日期预测将使用什么）。

Select 'Comp. Usage' as Flag
     , '2' as Sort
     , [Item No_]
     , [Location Code] as 'Loc'
     , [Prod_ Order No_] as 'Doc. No.'
     , -SUM([Remaining Qty_ (Base)]) as 'Qty'
     , [Due Date]
      , '' as ExpDate
     , '' as 'Contract'
     , '' as 'Ship Mthd'
From OIC_LIVE.dbo.[Oregon Ice Cream$Prod_ Order Component]
Where Status in ('1','2','3')
 and [Item No_] = 'IM02811'
 and [Remaining Quantity] <> 0
Group By [Item No_], [Location Code], [Prod_ Order No_], [Due Date]

在此处输入图像描述 这是我目前按批次编号（Doc No）的项目 IM02811 的库存以及到期日期。

Select 'Expire' as Flag
     , '2' as Sort
     , ia.[Item No_]
     --, ia.[Location Code] as 'Loc'
     , ia.[Lot No_] as 'Doc No'
     , Sum(ia.[Available Qty_ (Base)]) as 'Qty'
     , ExpDt.[Expiration Date] as 'ExpDate'
     , '' as Contract
     , '' as 'Ship Mthd'
From OIC_LIVE.dbo.[Oregon Ice Cream$Item Availability View] ia
Left Outer Join (Select [Item No_]
                     , [Lot No_]
                     , Max([Entry No_]) as EntryNo
                     , [Expiration Date]
                From OIC_LIVE.dbo.[Oregon Ice Cream$Item Ledger Entry]
                Where [Expiration Date] <> '1753-01-01 00:00:00.000'
                Group by [Item No_]
                    , [Lot No_]
                    , [Expiration Date]) ExpDt
 On ia.[Item No_] = ExpDt.[Item No_]
 and ia.[Lot No_] = ExpDt.[Lot No_]
Where  ExpDt.[Expiration Date] >= Getdate()
 and ia.[Item No_] = 'IM02811'
Group by ia.[Item No_]
     --, ia.[Location Code] 
     , ia.[Lot No_] 
     , ExpDt.[Expiration Date]

在此处输入图像描述

假设先到期先出 (FEFO)，我需要预测批次使用情况并计算剩余库存。在本例中，我们将全部 50 手 F021287 和 464.26191 磅 F022667 提供订单要求的 514.26191 磅。换句话说，我们将消耗除 535.7381 之外的所有批次 F02267。我需要我的 SQL 将 535.7381 作为计划预测后的计划库存按批次返回。

在此处输入图像描述

这是一个愚蠢的例子……想象一下十几个预定的生产订单和十几个具有不同到期日期的批次，我需要从数学上减去 FEFO 作为预计消耗量。我觉得我可能正在查看一个光标来排列产品订单并开始对每批进行数学运算，以预测每批的未来库存。一旦计划出来，我可以简单地退回未计划使用的批次及其到期日期。

标签： sqltsql

解决方案

要回答上面评论中的问题——放入临时表比游标更好，更好的是找到一种完全没有循环的方法——SQL 是一种适用于数据集的语言，你必须在集合中思考，而不是在控制和循环流中。

例如，相对于您上面的代码，您可以使用左连接来减少对循环的需求，如下所示：

SELECT C.[item no_] AS ITEM_NO, C.Qty AS QTY, C.[Doc. No.] AS DOC_NUM,
       I.QTY AS SMALLEST_QTY
From #Comp AS C
LEFT JOIN (
   -- Here we select smallest by date  
   SELECT QTY, [Doc No] AS DOC_NUM, [Item No_] AS ITEM_NO,
        ROW_NUMBER() OVER (PARTITION BY [Item No_] ORDER BY ExpDate AS RN
   FROM #Inv 
) I ON I.ITEM = C.COMP_ITEM AND RN = 1

现在你可以看看这个集合，看看哪里 SMALLEST_QTY 小于 QTY

注意：我没有“测试”这一点，也没有仔细查看您的所有规则……这只是为了向您展示如何使用 SQL 来操作集合而不是单个记录。

另一个注意事项——你可能无法改变它——但你的名字不好。确实没有理由在列名中包含空格和大小写。它只会让工作变得更加困难。无论您用作前端的任何内容都可以格式化您的列名，并且无论如何您都不会在报告中使用“Item No_”。

推荐阅读