时间戳

首页 > 解决方案 > SQL Server：返回现有库存单位的最新成本修订的子查询

sql-server - SQL Server：返回现有库存单位的最新成本修订的子查询

问题描述

我有 2 张表，我试图将它们组合在一起以得到一个结果，它告诉我手头上的物品的标准成本（基于 FIFO 成本计算方法）。第一个表是存货收据，它告诉我剩下要消耗的零件以及这些收据的交易日期。第二个是标准成本视图，它告诉我项目的成本历史（rev = 修订号，每次更新零件的标准成本时增加 1）。

我目前有一个使用 TOP 1 并在成本生效日期按 DESC 订购的解决方案，但是，当我为公司的整个库存清单运行此程序时，由于 TOP 1 子查询效率低下，需要 16 分钟以上成本。

样本数据（现有库存收据）：

partID  warehouse     transDate  seqn orderID   qtytoconsume
-------------------------------------------------------------
P0003   W01 2019-01-24 00:00:00.000 1   ORD0187 2
P0003   W01 2018-06-24 00:00:00.000 1   ORD0099 3
P0003   W01 2018-11-24 00:00:00.000 1   ORD0165 1
P0003   W04 2018-12-14 00:00:00.000 1   ORD0175 1
P0002   W02 2019-01-14 00:00:00.000 1   ORD0184 4
P0002   W02 2019-03-24 00:00:00.000 1   ORD0199 1
P0002   W03 2018-05-27 00:00:00.000 1   ORD0093 1
P0002   W03 2018-12-06 00:00:00.000 1   ORD0171 2
P0001   W04 2018-09-09 00:00:00.000 1   ORD0146 5
P0001   W02 2019-04-22 00:00:00.000 1   ORD0200 4
P0001   W03 2019-03-29 00:00:00.000 1   ORD0200 2
P0001   W02 2018-02-14 00:00:00.000 1   ORD0061 1

和标准成本视图：

partID  document    effdate            rev  costamt
-----------------------------------------------------
P0001   IV0001  2018-01-28 00:00:00.000 1   1000.00
P0001   IV0023  2018-06-30 00:00:00.000 2   1200.00
P0001   IV0045  2019-01-01 00:00:00.000 3   1300.00
P0002   IV0001  2018-01-28 00:00:00.000 1   45.00
P0002   IV0013  2018-04-10 00:00:00.000 2   42.00
P0002   IV0045  2019-01-01 00:00:00.000 3   56.00
P0003   IV0001  2018-01-28 00:00:00.000 1   23400.00
P0003   IV0003  2018-02-20 00:00:00.000 2   11200.00
P0003   IV0045  2019-01-01 00:00:00.000 3   15000.00
P0003   IV0047  2019-02-27 00:00:00.000 4   13400.00
P0003   IV0078  2019-05-03 00:00:00.000 5   14670.00

我的结果（等于我的预期结果），但对于大行集来说并不理想。

partID  warehouse   transDate     seqn  orderID qty costamt
-------------------------------------------------------------
P0003   W01 2019-01-24 00:00:00.000 1   ORD0187 2   15000.00
P0003   W01 2018-06-24 00:00:00.000 1   ORD0099 3   11200.00
P0003   W01 2018-11-24 00:00:00.000 1   ORD0165 1   11200.00
P0003   W04 2018-12-14 00:00:00.000 1   ORD0175 1   11200.00
P0002   W02 2019-01-14 00:00:00.000 1   ORD0184 4   56.00
P0002   W02 2019-03-24 00:00:00.000 1   ORD0199 1   56.00
P0002   W03 2018-05-27 00:00:00.000 1   ORD0093 1   42.00
P0002   W03 2018-12-06 00:00:00.000 1   ORD0171 2   42.00
P0001   W04 2018-09-09 00:00:00.000 1   ORD0146 5   1200.00
P0001   W02 2019-04-22 00:00:00.000 1   ORD0200 4   1300.00
P0001   W03 2019-03-29 00:00:00.000 1   ORD0200 2   1300.00
P0001   W02 2018-02-14 00:00:00.000 1   ORD0061 1   1000.00

我的查询是：

SELECT 
    ioh.*, sc.costamt, sc.effdate
FROM
    inventoryonHand ioh
LEFT JOIN 
    standardcosts sc ON sc.partID = ioh.partID
                     AND sc.effdate = (SELECT TOP 1 sc2.effDate
                                       FROM standardcosts sc2
                                       WHERE sc2.partID = sc.partID
                                         AND sc2.effDate < ioh.transDate
                                       ORDER BY sc2.partID ASC, sc2.effDate DESC);

非常感谢你们！

标签： sql-serversubqueryfifo

解决方案

您可以尝试使用max().

(SELECT max(sc1.effdate)
        FROM standardcosts sc2
        WHERE sc2.partid = sc.partid
              AND sc2.effdate < ioh.transdate)

为了提高性能，请尝试在standardcosts (partid ASC, effdate DESC).

推荐阅读