问运行总库存性能较低- SQL Server 2016

EN

with cte_1 -- Get Total Stock Position not Grouped
as
(
    SELECT [CmpID],[EntryDate] ,[ProductID],[ColourCode]
    ,SUM([Quantity]) OVER(PARTITION BY [CmpID],[ProductID],[ColourCode] ORDER BY [EntryDate]) [TOTAL Running Quantity]
    FROM [dbo].[Inventory]
)
,cte_2 -- Get Stock Breakdown for each Day from cte_1
as
(
    SELECT a.[CmpID],a.[EntryDate],a.[ProductID],a.[ColourCode],a.[TOTAL Running Quantity],b.[LocGroup],MAX(b.[EntryDate]) as [MaxDate]
    FROM cte_1 a
    LEFT OUTER JOIN [dbo].[Inventory] b
    on a.[CmpID] = b.[CmpID]
    and a.[ProductID] = b.[ProductID]
    and a.[ColourCode] = b.[ColourCode]
    and b.[EntryDate] <= a.[EntryDate]
    GROUP BY a.[CmpID],a.[EntryDate],a.[ProductID],a.[ColourCode],a.[TOTAL Running Quantity],b.[LocGroup]
)

SELECT a.[CmpID],a.[EntryDate],a.[ProductID],a.[ColourCode],a.[TOTAL Running Quantity],a.[LocGroup],b.[Running Quantity]
FROM cte_2 a
LEFT OUTER JOIN [dbo].[Inventory] b
on a.[CmpID] = b.[CmpID]
and a.[ProductID] = b.[ProductID]
and a.[ColourCode] = b.[ColourCode]
and a.[LocGroup] = b.[LocGroup]
and b.[EntryDate] = a.[MaxDate]
ORDER BY a.[ColourCode], a.[EntryDate], a.[LocGroup]