Search code examples
sqljoinderived-table

SQL - Derived column from separate tables

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:

Current

The desired output would be this:

desired output

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

Solution

  • Wouldn't a CASE expression work in this situation?

    Something like:

    SELECT
       CASE
              WHEN sage sku IS NULL THEN wms sku
              ELSE sage sku
       END AS sku

    That should return a calculated column sku from multiple source-columns, based on which is or isn't empty.