CODEWITHSUNDEEP
sql-servert-sqlunion
Stuart
Stuart

Reputation: 688

SQL Server : UNION, Columns with Data, Drop the Columns Without

I have two tables, LYEAR and CYEAR:

[SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Columns [SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8] are populated with information from the CYEAR table and [SKU],[Title],[9],[10],[11],[12] from the LYEAR table.

How would I merge the two tables, so that there are columns with NULL values are dropped, leaving me with one row per SKU?

I have got this far:

WITH LYEAR_ORG ([SKU],[Title],[QTY],[DATE]) AS 
(
    SELECT 
        T1.ItemNumber, T1.ItemTitle, (SUM(T2.nqty)) AS [UNITS], 
        CONVERT(VARCHAR(2), (MONTH(T3.dProcessedOn)))
    FROM 
        StockItem T1
    LEFT OUTER JOIN 
        OrderItem T2 ON T1.pkstockItemId = T2.fkStockItemID_processed
    LEFT JOIN 
        [Order] T3 ON T3.pkOrderID = T2.fkOrderID
    WHERE   
        (YEAR(T3.dProcessedOn)) = (YEAR(getdate())-1) 
        AND (MONTH(T3.dProcessedOn)) > (MONTH(getdate())-1)
    GROUP BY 
        T1.ItemNumber, T1.ItemTitle, 
        CONVERT(VARCHAR(2), (MONTH(T3.dProcessedOn)))
),
CYEAR_ORG ([SKU],[Title],[QTY],[DATE]) AS 
(
    SELECT T1.ItemNumber, T1.ItemTitle, (SUM(T2.nqty)) AS [UNITS], CONVERT(varchar(2),(MONTH(T3.dProcessedOn)))
    FROM StockItem T1
    LEFT OUTER JOIN OrderItem T2 ON T1.pkstockItemId = T2.fkStockItemID_processed
    LEFT JOIN [Order] T3 ON T3.pkOrderID = T2.fkOrderID
    WHERE   (YEAR(T3.dProcessedOn)) = (YEAR(getdate())-1) 
            AND (MONTH(T3.dProcessedOn)) < (MONTH(getdate()))
    GROUP BY T1.ItemNumber, T1.ItemTitle, CONVERT(varchar(2),(MONTH(T3.dProcessedOn)))
    ),

LYEAR ([SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) AS (
    SELECT *
    FROM LYEAR_ORG
    PIVOT ( MAX(QTY) FOR DATE in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as LYEAR_ORDERS
    ),

CYEAR ([SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) AS (
    SELECT *
    FROM CYEAR_ORG
    PIVOT ( MAX(QTY) FOR DATE in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as CYEAR_ORDERS
    )

SELECT * FROM LYEAR
UNION ALL
SELECT * FROM CYEAR

I am unable to use INSERT INTO as this is a limitation on the Linnworks platform I am working on, it is SQL Server.

Upvotes: 0

Views: 75

Answers (2)

Leia
Leia

Reputation: 190

Here's a solution that will allow for there to be data in both LYEAR and CYEAR, and to switch from one to the other based on the month of the year:

            SELECT  SKU,
                    Title,
                    CASE    WHEN DATEPART(MM, GETDATE()) > 1
                                THEN LYEAR.1
                                ELSE CYEAR.1
                                END,
                    CASE    WHEN DATEPART(MM, GETDATE()) > 2
                                THEN LYEAR.2
                                ELSE CYEAR.2
                                END,
                    CASE    WHEN DATEPART(MM, GETDATE()) > 3
                                THEN LYEAR.3
                                ELSE CYEAR.3
                                END,
                    CASE    WHEN DATEPART(MM, GETDATE()) > 4
                                THEN LYEAR.4
                                ELSE CYEAR.4
                                END
                    --Add rest of CASE statements for remaining months
            FROM    LYEAR AS L
                    JOIN CYEAR AS C
                    ON  L.SKU = C.SKU
                    AND L.Title = C.Title

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

On the assumption that LYEAR columns 1-8 are all NULL, and so are CYEAR 9-12, a JOIN with a ton of COALESCEs, rather than a UNION will get you where you want to be. If, though, there are values for both years in any columns, this will go sideways.

SELECT
  l.[SKU]
 ,l.[Title]
 ,COALESCE(l.[1], c.[1]) AS [1]
 ,COALESCE(l.[2], c.[2]) AS [2]
 ,COALESCE(l.[3], c.[3]) AS [3]
 ,COALESCE(l.[4], c.[4]) AS [4]
 ,COALESCE(l.[5], c.[5]) AS [5]
 ,COALESCE(l.[6], c.[6]) AS [6]
 ,COALESCE(l.[7], c.[7]) AS [7]
 ,COALESCE(l.[8], c.[8]) AS [8]
 ,COALESCE(l.[9], c.[9]) AS [9]
 ,COALESCE(l.[10], c.[10]) AS [10]
 ,COALESCE(l.[11], c.[11]) AS [11]
 ,COALESCE(l.[12], c.[12]) AS [12]
FROM
  LYEAR AS l
  JOIN
    CYEAR AS c
      ON
      c.SKU = l.SKU
      AND c.Title = l.Title;

Upvotes: 2

Related Questions