SQL - Derived column from separate tables-CodePudding

I have two separate CTE tables containing stock data with a full join. While this works to show all records form both tables I need to find a way to come up with a single LOCATION and SKU column which combines the sku and location columns from each table.

The current output looks like this:

The desired output would be this:

Here is the current code:

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

I'm really not sure how to achieve this. I thought one option may be to create a third table with every possible location and sku combination and join to that. However, that will be a huge table with at least 2 million rows and a pain to maintain. Any ideas on how to proceed greatly appreciated

CodePudding user response：

Wouldnt case work in this situation?

Something like

Select case when SAGE SKU is null then WMS SKU else SAGE SKU end as SKU

that should fill your final column from multiple source columns, based on which is or isnt empty