我有兩個單獨的 CTE 表，其中包含具有完整連接的股票資料。雖然這可以顯示兩個表中的所有記錄，但我需要找到一種方法來提供單個 LOCATION 和 SKU 列，該列組合了每個表中的 sku 和位置列。
當前輸出如下所示：
所需的輸出將是這樣的：
這是當前代碼：
WITH WMSQty AS
(SELECT * FROM (
SELECT [Date], Warehouse, Location, Sku,
SUM (CASE
WHEN Warehouse = 'SYNCUK' AND [Location] = 'SYNCUK' THEN QtyOnHand
WHEN Warehouse = 'SYNCUK' AND [Location] = 'SYUKSC' THEN QtyOnHand
WHEN Warehouse = 'SYNCUK' AND [Location] = 'SYNCRE' THEN QtyOnHand
WHEN Warehouse = 'SYNCNL' AND [Location] = 'SYNCNL' THEN QtyOnHand
WHEN Warehouse = 'SYNCNL' AND [Location] = 'SYNLSC' THEN QtyOnHand
WHEN Warehouse = 'SYNCNL' AND [Location] = 'SYNLRE' THEN QtyOnHand
WHEN Warehouse = 'SYNCAM' AND [Location] = 'SYNCAM' THEN QtyOnHand
WHEN Warehouse = 'SYNCAM' AND [Location] = 'SYAMSC' THEN QtyOnHand
WHEN Warehouse = 'SYNCAM' AND [Location] = 'SYAMRE' THEN QtyOnHand
WHEN Warehouse = 'SYNCAM' AND [Location] = 'SYAMAZ' THEN QtyOnHand
WHEN Warehouse = 'SHPWIR' AND [Location] = 'SHPWIR' THEN QtyOnHand
WHEN Warehouse = 'SHPWIR' AND [Location] = 'SHPWRD' THEN QtyOnHand
ELSE 0
END) AS QtyOnHand,
RANK() OVER (PARTITION BY Warehouse ORDER BY [DATE] DESC) date_rank
FROM [ReportTestCA].[dbo].[DimWMSData]
GROUP BY [Date],warehouse,Location,Sku) t
WHERE date_rank = 1),
SAGE AS
(SELECT
AUDTORG, [DATE], ITEMNO, LOCATION, QTYONHAND, LASTCOST
FROM DimSageLocationData
WHERE DimSageLocationData.[DATE] = (SELECT MAX(DimSageLocationData.[DATE]) FROM
DimSageLocationData WHERE DimSageLocationData.[DATE] < GETDATE())
)
SELECT
SAGE.AUDTORG,
SAGE.[DATE] AS [SAGE DATE],
WMSQty.[Date] AS [WMS DATE],
SAGE.ITEMNO AS [SAGE SKU],
SAGE.LOCATION AS [SAGE LOCATION],
SAGE.QTYONHAND AS [SAGE QTY],
SAGE.LASTCOST AS [SAGE LASTCOST],
WMSQty.Warehouse AS [WMS WAREHOUSE],
WMSQty.Sku AS [WMS SKU],
WMSQty.Location AS [WMS LOCATION],
WMSQty.QtyOnHand AS [WMS QTY],
SAGE.QTYONHAND - WMSQty.QtyOnHand AS [UNIT VARIANCE]
FROM
SAGE FULL JOIN
WMSQty ON WMSQty.Location = SAGE.Location AND SAGE.ITEMNO = WMSQty.Sku
WHERE (SAGE.ITEMNO = 'T21-8633' OR WMSQty.Sku = 'T21-8633')
ORDER BY SAGE.LOCATION, SAGE.ITEMNO, WMSQty.Location, WMSQty.Sku ASC
我真的不確定如何實作這一目標。我認為一個選擇可能是創建第三個表，其中包含每個可能的位置和 sku 組合并加入其中。然而，這將是一個至少有 200 萬行的巨大表，并且維護起來很麻煩。非常感謝任何關于如何進行的想法
uj5u.com熱心網友回復：
案例在這種情況下行不通嗎？
就像是
選擇當 SAGE SKU 為空時的情況然后 WMS SKU 否則 SAGE SKU 結束為 SKU
那應該從多個源列中填充你的最后一列，基于哪個是或不是空的
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/534871.html
標籤：数据库加入派生表