SELECT one column twice from same table with two WHERE conditions SQL SERVER

Question

I am trying to figure out the simplest way to SELECT a column twice from the same table but to show it with two different WHERE conditions side by side as part of a larger query (below). What would be the best way to achieve this?

USE PDX_SAP_USER 

GO 

SELECT          P.PLANT_CODE,
                P.STOCK_CATEGORY,
                P.MATERIAL,
                P.DISTRIBUTION_VERSION_CODE,
                P.PERIOD_CODE,
                P.REQUIREMENTS_DATE,
                P.PLANNED_QTY AS 'REM PLAN QTY',
                P.VERSION_IND_FLAG,
                P.SIZE_LITERAL,
                P.WITHDRAWN_QUANTITY,
                P.TECHNICAL_INDEX,
                P.PLANNED_QTY + P.WITHDRAWN_QUANTITY AS 'ORIGINAL FCST QTY'
 FROM           VW_PLANNED_REQMNTS_TXT P
 WHERE          P.PLANT_CODE IN ('6040','6041')
 AND            P.STOCK_CATEGORY IN ('A60385000','A60385003')
 AND            P.DISTRIBUTION_VERSION_CODE IN ('00','01','ZU','Z2')
 AND            P.REQUIREMENTS_DATE < GETDATE() - 59
 AND            P.PLANNED_QTY > 0 
 ORDER BY       P.PLANT_CODE,
                P.STOCK_CATEGORY,
                P.MATERIAL,
                P.REQUIREMENTS_DATE,
                P.TECHNICAL_INDEX

Solution 1

Depending the situation, there are a few different strategies to conditionally exclude specific column data from the query.

If one is a subset of the other, you can use CASE to exclude unwanted values

SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,PS.ProductQty
   ,CASE 
      WHEN PS.ShipmentDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE()) 
         THEN PS.ProductQty 
    END AS ProductQtyThisMonth
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate > DATEADD(MONTH, -1, GETDATE())

If the where clauses aren't overlapping, then a UNION ALL may be a better choice. You could also OR both conditions together and add a case for each return.

SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,PS.ProductQty AS ProductQtyAncient
   ,NULL AS ProductQtyFuturistic
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate < DATEADD(YEAR, -1, GETDATE())
UNION ALL
SELECT
   PS.ProductShipmnetId
   ,PS.ShipmentDate
   ,NULL AS ProductQtyAncient
   ,PS.ProductQty AS ProductQtyFuturistic
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate > DATEADD(YEAR, 1, GETDATE())

I more commonly see these techniques used with aggregate functions and you mention that this is part of a larger query, so I want to make sure you're aware of that as well.

SELECT
   PS.ProductId
   ,SUM(CASE WHEN PS.ShipmentDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE())
           THEN PS.ProductQty END) AS ProductQtyThisMonth
   ,SUM(CASE WHEN PS.ShipmentDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
           THEN PS.ProductQty END) AS ProductQtyLastMonth
FROM
   ProductShipment PS
WHERE
   PS.ShipmentDate BETWEEN DATEADD(MONTH, -1, GETDATE()) 
       AND DATEADD(MONTH, 1, GETDATE())
GROUP BY
   PS.ProductId

Solution 2

You can use this "template" for select joins:

SELECT * -- <your final query, use Q1.field and Q2.field>
FROM 
    (SELECT <query1> WHERE <condition1>) Q1
LEFT JOIN
    (SELECT <query2> WHERE <condition2>) Q2
-- add more joins if necessary
ON
    Q1.pk = Q2.pk

the only remaining part is to identify primary key on which selects will join.

P.S. of course it's not necessarily only LEFT JOIN, it's just what you want usually.

Comments

[user3496218]

Thank you, I think this gives me another option I hadn't even thought about - much appreciated.

[user3496218]

Thank you for all of these ideas I think the UNION is what I am looking for as it allows me to run essentially the same query but with that one clause different. My only question is - will it need to display all columns for both statements or could I only display certain columns (such as only the PLANNED_QTY from the second part of the UNION) I will continue digging into this further but appreciate the push in the right direction.

[AlwaysLoadingData]

@user For unions, the column signature needs to match between the joined queries, but it doesn't necessarily have to be the same. If there are columns that don't make sense, you could replace those with NULL or constant values.