Sql-server – Subtracting from multiple CASE statements

casesql serversql-server-2008

I'm trying to get the net sales from the query below in SQL Server 2008.

We identify a Sale as Type 1 and a Credit as Type 8.

What I'd like to do is get the Sum of the Sales and Subtract the Sum of the Credits to get the NetSales, but I haven't had any luck.

I'm not really sure if I should be using the CASE statement to get the NetSales. I'd like to see the below from the query, or even with just the NetSales showing:

Wanted Result

And here is the query:

SELECT TOP (100) PERCENT IM.IM_PROD_CODE, IV.IV_CUKEY, IM.IM_KEY, IM.IM_DESCR,
   SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END) AS Sales,
 SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END) AS Credits

FROM IV INNER JOIN
  SH ON IV_ENTITY_ID = SH.SH_IVENTITY_ID AND IV.IV_NUMBER = SH.SH_IVNUMBER INNER JOIN
   IM ON SH.SH_ITEM = IM.IM_KEY
WHERE 
   (IV.IV_TYPE = 1 OR IV.IV_TYPE = 8) AND (IM.IM_PROD_CODE = 'ESY') AND   (SH.SH_DATE >= CONVERT(DATETIME, '2015-11-01 00:00:00', 102))

GROUP BY 
   IV.IV_CUKEY, IM.IM_PROD_CODE, IM.IM_KEY, IM.IM_DESCR, IV.IV_TYPE
HAVING  
   (IM.IM_KEY = 'A-05.000.007.01_LM')

Best Answer

So you want the result of the second SUM to be subtracted from the first SUM. One option would be to do just that:

SELECT TOP (100) PERCENT
  IM.IM_PROD_CODE,
  IV.IV_CUKEY,
  IM.IM_KEY,
  IM.IM_DESCR,
  Sales    = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END),
  Credits  = SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END),
  NetSales = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END)
           - SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END)
FROM
  … /* the rest of your query */

Yes, you are repeating the SUM expressions, but that is fine, there are only two of them. The DRY principle applicable to many other languages is less relevant in SQL, where duplication of code can be a perfectly normal way to achieve better performance.

In this case, however, the duplication can be avoided by using a derived table:

SELECT
  IM_PROD_CODE,
  IV_CUKEY,
  IM_KEY,
  IM_DESCR,
  Sales,
  Credits,
  NetSales = Sales - Credits
FROM
  (
    … /* the entirety of your current query */
  ) AS derived;

I would also like to suggest, if I may, that you be consistent in how you specify your constant literals. What I mean is sometimes in your query you are providing the matched values for IV.IV_TYPE as strings (IV.IV_TYPE = '1') and other times as numbers (IV.IV_TYPE = 1). You should really choose one way and, of course, it should be the one matching the column's actual type.

Also, the TOP (100) PERCENT in your query makes little sense. It looks as though it may be a remnant of an old technique for intermediate materialisation, which, however, may no longer be working in your version of SQL Server.

There is also this HAVING filter in your query (HAVING (IM.IM_KEY = 'A-05.000.007.01_LM')), which would work more efficiently if you moved the condition to the WHERE clause.

One last note concerns your second CASE expression (the Type 8 one). One of the columns it is referencing is qualified with an alias that is not found in your FROM clause: PK1.SH.SH_PRICE. I am assuming that is some kind of a copy-paste error posting your script here but I thought I would let you know so that you could correct it.

Related Question