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:
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')