sql-server - 减去 SUM 的耗尽和限制

问题描述

首先我使用 AdventureWork2019 作为参考

我有一个要加入 5 个表的查询

USE [AdventureWorks2019]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE dbo.TestLocation
@UseDate DateTime
AS
BEGIN
    SET NOCOUNT ON;

SELECT prodID
,SUM(PurchQty) AS TotalPurchase
,SUM(SalesQty) AS TotalSell
,StartDate
from (
SELECT DISTINCT  WO.ProductID AS prodID
, StartDate
,WO.OrderQty AS PurchQty
,SOD.OrderQty AS SalesQty
FROM Sales.SalesOrderDetail SOD
LEFT JOIN Production.WorkOrderRouting WOR ON WOR.ProductID = SOD.ProductID

--LEFT JOIN Production.Location PL ON PL.LocationID = WOR.LocationID
--The above Join is the one for the locationID and it's working Fine
LEFT JOIN Production.WorkOrder WO ON WO.ProductID = SOD.ProductID
FULL OUTER JOIN Purchasing.PurchaseOrderDetail POD ON POD.ProductID = SOD.ProductID

WHERE  StartDate = @UseDate 
-- AND  PL.LocationID >= 10
) Test3
Group by prodID,StartDate
order by prodID ASC, StartDate

END
GO


EXEC TestLocation '2011-07-02 00:00:00.000'


Output(sample):

prodID  TotalPurc   TotalSell   StartDate
717         8         36    2011-07-02 00:00:00.000
730         9         47    2011-07-02 00:00:00.000
744         2         3     2011-07-02 00:00:00.000
747         12        21    2011-07-02 00:00:00.000
749         5         15    2011-07-02 00:00:00.000
761         16        138   2011-07-02 00:00:00.000
775         26        91    2011-07-02 00:00:00.000
777         12        78    2011-07-02 00:00:00.000
802         6         21    2011-07-02 00:00:00.000
804         40        60    2011-07-02 00:00:00.000
806         16        138   2011-07-02 00:00:00.000
807         24        23    2011-07-02 00:00:00.000
810         21        28    2011-07-02 00:00:00.000
811         6         21    2011-07-02 00:00:00.000
813         8         37    2011-07-02 00:00:00.000
817         21        28    2011-07-02 00:00:00.000

以及 LocationID 的另一个表（作为仓库）

SELECT LocationID,CostRate,Availability 
FROM Production.Location
WHERE LocationID >= 10
order by CostRate ASC

LocationID  CostRate    Availability
50           12.25        120.00
60           12.25        120.00
30           14.50        120.00
40           15.75        120.00
45           18.00        80.00
10           22.50        96.00
20           25.00        108.00

我想要做的是把每个 LoactionId和ProdID带到TotalPurc位置并减少Availability列中的数量，每个TotalSell都会增加可用性列。最大Availability数量为 130。如果所有位置都没有可用数量，即所有位置的可用数量为 0，则它将停止。

以上将适用于指定的日期，因为您可以检查查询并运行它，如果您有 AdventureWork2019

简单的输出来检查我希望数据如何：

prodID  TotalPurc   TotalSell   StartDate
    717         8         36    2011-07-02 00:00:00.000
    730         9         47    2011-07-02 00:00:00.000
    744         2         3     2011-07-02 00:00:00.000
    747         12        21    2011-07-02 00:00:00.000
    749         5         15    2011-07-02 00:00:00.000

LocationID  CostRate    Availability
50           12.25        120.00
60           12.25        120.00
30           14.50        120.00
40           15.75        120.00
45           18.00        80.00
10           22.50        96.00
20           25.00        108.00


Output :

     prodID TotalPurc   TotalSell   StartDate                  LocationID   Availability  Remaining 

        717         8         36    2011-07-02 00:00:00.000          50            130       18
        717         8         36    2011-07-02 00:00:00.000          60            130       8
        717         8         36    2011-07-02 00:00:00.000          30            128       0
--what happened above is that I took the (120-8) = 112 then 112+36 = 148 we only can use 130 then the remaining is 18 then we took the next `LocationID` with the least Cost (120+18 = 138 we can use 130 so we took the 8) and used it in the next `LocationID`
        730         9         47    2011-07-02 00:00:00.000          30            130      36                       
        730         9         47    2011-07-02 00:00:00.000          40            130      26
        730         9         47    2011-07-02 00:00:00.000          45            106      0
        744         2         3     2011-07-02 00:00:00.000          45            107      0
        747         12        21    2011-07-02 00:00:00.000          45            116      0
        749         5         15    2011-07-02 00:00:00.000          45            126      0

--the above is the same as the first 3 rows we subtract and add to the availability

另一个条件是，如果所有位置都达到 0 或 130，则停止

我怎样才能在 SQL Server 中做到这一点？我尝试使用 CTE，但效果不佳，并尝试了我认为最适合这类事情的光标，但没有取得任何成果。

先感谢您

编辑：

ALTER FUNCTION GetStockMovment 
(
    -- Add the parameters for the function here
@ForDate Datetime
)
  RETURNS @Sums TABLE (
        RemoveQTY Numeric(24, 7),
        ADDQTY Numeric(24, 7) 
    )
AS
BEGIN
Declare @WoSum Numeric(24, 7),
@SODSUM Numeric(24, 7),
@WORSum Numeric(24, 7),
@PODSum Numeric(24, 7)

select @SODSUM = SUM(SOD.OrderQty) from Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
where SOH.OrderDate = @ForDate

select @WoSum = sum(orderQty) from Production.WorkOrder
where StartDate = @ForDate


select @PODSum = sum(POD.OrderQty) from Purchasing.PurchaseOrderDetail POD
INNER JOIN Purchasing.PurchaseOrderHeader POH ON POD.PurchaseOrderID = POH.PurchaseOrderID 
where POH.OrderDate = @ForDate


select @WoSum = sum(WO.OrderQty) from Production.WorkOrder WO
where WO.DueDate = @ForDate

INSERT INTO @Sums (RemoveQTY,ADDQTY)
SELECT isnull(@SODSUM,0) + isnull(@WORSum,0) , isnull(@PODSum,0) + isnull(@WoSum,0)
    RETURN;
END;
GO

select * from  dbo.GetStockMovment ('2014-05-26 00:00:00.000')

输出：

RemoveQTY   ADDQTY
189.0000000 5334.0000000

标签： sql-serverloopsstored-procedures

sql-server - 减去 SUM 的耗尽和限制

问题描述

解决方案

推荐阅读