Simplify multiple use of same CASE WHEN sequence

Question

This is an under-performing query that I've been trying to simplify but am at a loss. Basically we are looking at Dynamics-SL (Solomon) tables to determine if something has shipped yet or not. Often people enter quantity as having shipped yet the event handling is incorrect as it hasn't actually left the warehouse. So we look at the SOEvent table for items with the 'NPAK' or 'PINV' type and then compare against the quantity shipped in the SOShipLine table... Problem is we do this again in the WHERE clause so we end up with this super long query that I gotta think can be simplified, here it is (TIA):

SELECT     

SOHeader.OrdNbr, 
SOHeader.CustID, 
SOHeader.User9 AS ShipDate,
SOLine.LineRef, 
SOLine.InvtID, 
SOLine.QtyOrd, 

CASE WHEN SOShipHeader.InvcNbr = '' THEN

CASE WHEN (
   SELECT MIN(EventTime)  
   FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
            WHERE  (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
            AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
THEN 0 

ELSE (
  SELECT MAX(SOShipLine.QtyShip) 
  FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
        WHERE SOShipLine.InvtID = SOLine.InvtID 
        AND SOShipLine.SiteID = SOLine.SiteID 
        AND SOShipHeader.ShipperID = SOShipLine.ShipperID
        )
END 

ELSE
  ISNULL(
     (SELECT MAX(SOShipLine.QtyShip) 
      FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
   WHERE SOShipLine.InvtID = SOLine.InvtID 
   AND SOShipLine.SiteID = SOLine.SiteID
   AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
,0)

END

AS QtyShip_Corrected,

ISNULL(
 (SELECT MAX(SOShipLine.QtyShip) 
  FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
     WHERE SOShipLine.InvtID = SOLine.InvtID 
     AND SOShipLine.SiteID = SOLine.SiteID 
     AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
   ,0)

AS SOShipLineQtyShip,

QtyShip

FROM Solomon_SOHeader SOHeader  WITH (NOLOCK) 
INNER JOIN Solomon_SOLine SOLine  WITH (NOLOCK) ON SOHeader.OrdNbr = SOLine.OrdNbr 
INNER JOIN Solomon_SOShipHeader SOShipHeader  WITH (NOLOCK) ON SOShipHeader.OrdNbr = SOHeader.OrdNbr 
AND SOShipHeader.Cancelled = 0

WHERE SOHeader.Status = 'O'
AND SOLine.QtyShip > 0
AND CASE WHEN SOShipHeader.InvcNbr = '' 

THEN

CASE WHEN (
  SELECT     MIN(EventTime)  
  FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
        WHERE (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
        AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
THEN 0 

 ELSE (
   SELECT MAX(SOShipLine.QtyShip) 
   FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
            WHERE SOShipLine.InvtID = SOLine.InvtID 
            AND SOShipLine.SiteID = SOLine.SiteID 
            AND SOShipHeader.ShipperID = SOShipLine.ShipperID
         )
 END 
ELSE

 ISNULL(
   (SELECT MAX(SOShipLine.QtyShip) 
    FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
             WHERE SOShipLine.InvtID = SOLine.InvtID 
             AND SOShipLine.SiteID = SOLine.SiteID 
             AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
       ,0)

END 

  <> ISNULL(
      (SELECT MAX(SOShipLine.QtyShip) 
       FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
    WHERE SOShipLine.InvtID = SOLine.InvtID 
    AND SOShipLine.SiteID = SOLine.SiteID 
    AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
 ,0)

Answer 1

Strip off the where clause and do the top part as a sub-query.

Then you can use a where clause against the column names. Something like this:

select * 
from  (
    SELECT     
     SOHeader.OrdNbr, 
     SOHeader.CustID, 
     SOHeader.User9 AS ShipDate,
     SOLine.LineRef, 
     SOLine.InvtID, 
     SOLine.QtyOrd, 

     CASE WHEN SOShipHeader.InvcNbr = '' THEN
      CASE WHEN (
          SELECT MIN(EventTime)  
          FROM  Solomon_SOEvent SOEvent WITH (NOLOCK)   
               WHERE  (SOEvent.EventType = 'NPAK' OR SOEvent.EventType = 'PINV') 
               AND SOEvent.OrdNbr = SOHeader.OrdNbr) IS NULL 
       THEN 0 

       ELSE (
         SELECT MAX(SOShipLine.QtyShip) 
         FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
              WHERE SOShipLine.InvtID = SOLine.InvtID 
              AND SOShipLine.SiteID = SOLine.SiteID 
              AND SOShipHeader.ShipperID = SOShipLine.ShipperID
              )
      END 

      ELSE
        ISNULL(
          (SELECT MAX(SOShipLine.QtyShip) 
           FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
         WHERE SOShipLine.InvtID = SOLine.InvtID 
         AND SOShipLine.SiteID = SOLine.SiteID
         AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
      ,0)
     END
     AS QtyShip_Corrected,

     ISNULL(
      (SELECT MAX(SOShipLine.QtyShip) 
       FROM Solomon_SOShipLine SOShipLine WITH (NOLOCK) 
          WHERE SOShipLine.InvtID = SOLine.InvtID 
          AND SOShipLine.SiteID = SOLine.SiteID 
          AND SOShipHeader.ShipperID = SOShipLine.ShipperID)
        ,0)

     AS SOShipLineQtyShip,1

     QtyShip

    FROM Solomon_SOHeader SOHeader  WITH (NOLOCK) 
    INNER JOIN Solomon_SOLine SOLine  WITH (NOLOCK) ON SOHeader.OrdNbr = SOLine.OrdNbr 
    INNER JOIN Solomon_SOShipHeader SOShipHeader  WITH (NOLOCK) ON SOShipHeader.OrdNbr = SOHeader.OrdNbr 
    AND SOShipHeader.Cancelled = 0

    WHERE SOHeader.Status = 'O'
    AND SOLine.QtyShip > 0
) a
where QtyShip_Corrected <> SOShipLineQtyShip