SQL Running total or Case statement on a running total

Question

I have a query that looks like this:

SELECT b.HH,
       b.TotalRevenues,
       b.TotalOperatingExpenses,
       b.TotalAUA,
       (b.TotalRevenues -b.TotalOperatingExpenses) AS Profit,
       ((b.TotalRevenues -b.TotalOperatingExpenses)/sum((b.TotalRevenues -b.TotalOperatingExpenses))) AS percentProfit FROM
  ( SELECT * FROM
     (SELECT CASE
                 WHEN (LTRIM(RTRIM(v.ISM_HH_KEY)) <> '')--OR
 --(LTRIM(RTRIM(v.ISM_HH_KEY)) IS NOT NULL)
 THEN LTRIM(RTRIM(v.ISM_HH_KEY))
                 ELSE LEFT(LTRIM(RTRIM(v.ACCOUNT)), 6)
             END AS 'HH', [WealthDB].[dbo].[WF_PM_SUM_DB].account AS 'Account', (sum(DB_1_1) + sum(DB_1_2) + sum(DB_1_3) + sum(DB_1_5) + sum(DB_2_1_1) + sum(DB_2_1_2) + sum(DB_2_2_1) + sum(DB_2_2_2)+SUM(DB_3_1)) AS 'TotalRevenues', SUM(DB_4) AS 'TotalOperatingExpenses', AVG(DB_10) AS 'TotalAUA'
      FROM [WealthDB].[dbo].[WF_PM_SUM_DB]
      INNER JOIN
        (SELECT DISTINCT ISM_HH_Key, account, Municipality
         FROM [WealthDB].[dbo].[WF_PM_ACCT_det_DB] ) AS v ON [WealthDB].[dbo].[WF_PM_SUM_DB].Account = v.Account
      WHERE [WealthDB].[dbo].[WF_PM_SUM_DB].yearmonth > '201512'
      GROUP BY v.account, V.ISM_HH_Key,[WealthDB].[dbo].[WF_PM_SUM_DB].account, v.Municipality --   (
 ) a
   GROUP BY a.HH, a.Account, a.HH, a.TotalRevenues, a.TotalOperatingExpenses,a.TotalAUA
   HAVING a.TotalAUA > 0
   AND a.TotalRevenues>0 ) b
GROUP BY b.HH,
         b.TotalRevenues,
         b.TotalOperatingExpenses,
         b.TotalAUA
HAVING (b.TotalRevenues -b.TotalOperatingExpenses) > 0
ORDER BY profit ASC

I get an output that looks like

Account     Revenue       Expenses   Profit     PercentageProfit
XXXX        $100,000       $20000    $15,000           1
YYY         $90000         $20000    $14,000           1

The percaentage profit is not being calculated right as it shows 1 in every row and also i want is another column that gives me a running total of the percentage profit in another column like:

Account     Revenue       Expenses   Profit     PercentageProfit  RunningTotal
XXXX        $100,000       $20000    $15,000           0.32%          0.32%
YYY         $90000         $20000    $14,000           0.29%          0.61%

The SQL server i work on is version 2000, so i cannot use the rank functions. Any suggestions. I dont think i can use OVER clauses so i cannot do a unbounded sum on the profit column to get the percent profit.

Also, once we have a running total column can i use case statements like

CASE WHEN RUNNINGTOTAL < 1% THEN 'TOP 1%'
CASE WHEN RUNNINGTOTAL >1% AND <20%   THEN '1-20%'
END AS 'SEGMENT'

Answer 1

((b.TotalRevenues -b.TotalOperatingExpenses)/sum((b.TotalRevenues -b.TotalOperatingExpenses)))

Will not give you percentage because both TotalRevenues and TotalOperatingExpenses were part of group by so it will never give you total Profit to calculate percentage

Try something like this

;WITH cte
     AS (SELECT b.HH,
                b.TotalRevenues,
                b.TotalOperatingExpenses,
                b.TotalAUA,
                ( b.TotalRevenues - b.TotalOperatingExpenses )                AS Profit,
                ( ( b.TotalRevenues - b.TotalOperatingExpenses ) / Sum(( b.TotalRevenues - b.TotalOperatingExpenses ))
                                                                     OVER() ) AS percentProfit
         FROM   (<<Inner query>>) b
         WHERE  ( b.TotalRevenues - b.TotalOperatingExpenses ) > 0)
SELECT *,
       Sum(percentProfit)OVER(ORDER BY profit desc) as RunningTotal
FROM   cte
ORDER  BY profit ASC 

If you are using older version of sql server then replace the final Select with

SELECT *,
       RunningTotal
FROM   cte
CROSS apply (SELECT Sum(percentProfit)
            FROM   cte b
            WHERE  b.percentProfit >= a.percentProfit) cs (RunningTotal) 
ORDER  BY profit ASC 

For SQL SERVER 2000

SELECT *,
       (SELECT Sum(percentProfit)
        FROM   (SELECT b.HH,
                       b.TotalRevenues,
                       b.TotalOperatingExpenses,
                       b.TotalAUA,
                       ( b.TotalRevenues - b.TotalOperatingExpenses )                AS Profit,
                       ( ( b.TotalRevenues - b.TotalOperatingExpenses ) / (Select Sum(( b.TotalRevenues - b.TotalOperatingExpenses )) from (<<Inner query>>))) AS percentProfit
                FROM   (<<Inner query>>) b
                WHERE  ( b.TotalRevenues - b.TotalOperatingExpenses ) > 0) b
        WHERE  b.percentProfit >= a.percentProfit) RunningTotal
FROM   (SELECT b.HH,
               b.TotalRevenues,
               b.TotalOperatingExpenses,
               b.TotalAUA,
               ( b.TotalRevenues - b.TotalOperatingExpenses )                AS Profit,
               ( ( b.TotalRevenues - b.TotalOperatingExpenses ) / (Select Sum(( b.TotalRevenues - b.TotalOperatingExpenses )) from (<<Inner query>>))) AS percentProfit
        FROM   (<<Inner query>>) b
        WHERE  ( b.TotalRevenues - b.TotalOperatingExpenses ) > 0) a