Sql-server – I have a beginning balance I need to calculate ending balance running total by month rows

Often with operations like running totals it is actually more efficient to use a cursor than some of the other methods. Don't be afraid of using a cursor unless you are seeing noticeable performance issues.

In SQL Server 2012 there are new window functions that make this better, but obviously you can't use those. There is a quirky update approach that "works" but the syntax isn't officially supported and might not work post-SQL Server 2012 - it may someday become illegal syntax and there is never any guarantee about how the ordering works. The typical set-based approaches don't scale well - with a cursor it is often better because you only have to scan each row once, whereas with a set-based solution the scanning grows non-linearly. I wish I could show you the work I've been doing on this, but the publication date of the blog post that will reveal it all is still unknown.

At the very least when you declare a cursor use:

DECLARE ... CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

This also might be useful reading:

• Bad habits to kick: Thinking a while loop isn't a cursor

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.