Rolling totals in a view I have a view that performs some rolling totals. In this instance, it must be in a view. The issue is that it takes about 10 minutes to run, returning 2700 records. There should be a faster way, but I am not privy at the moment. I asked a question here in order to get this far.

I am running SqlServer 2008 R2, so advanced over() functionality doesn't work, just Partition by Order by)

The difference between my question and what code I have below is that my requirements changed slightly, requiring that the OutOfPocket not be a total, but rather simply reflect the amount of the deduction (IssCost) once the $400 is exhausted. The only exception is on the deduction that exhausts the $400, the leftover is displayed, not the full deduction. A CROSS APPLY was used to achieve this effect. I couldn't quite figure out how to achieve the new requirement based upon the answer I selected in my question linked above, so I used a different answer that worked.

The concept of the view is that someone starts with $400 to spend. Every deduction (IssCost in the code), deducts from the initial $400. If a person starts going over the $400, the money will come from their own pocket (OutOfPocket).

Some sample data and the view code are as follows:

CREATE TABLE Payroll (
EmployeeID int,
PlanCode varchar(10),
IssCost int NULL
)
GO

INSERT INTO Payroll (EmployeeID, PlanCode, IssCost)
VALUES (1, 'Medical', 200)
  ,(1, 'Dental', 250)
  ,(1, 'Vision', 300)
  ,(2, 'Medical', 100)
  ,(2, 'Dental', 150)
  ,(2, 'Vision', 100)
  ,(2, 'Disability', 100)
  ,(2, 'Life', 140) 

  --View starts here
  WITH C AS (
    SELECT EmployeeID
          ,PlanCode
          ,IssCost
          ,rownum = ROW_NUMBER() over (Partition BY EmployeeID Order by PlanCode)
    FROM Payroll

), C2 AS (
    SELECT EmployeeID
          ,PlanCode
          ,IssCost
          ,T.Ytd
    FROM C
    CROSS APPLY (SELECT ytd = SUM(IssCost)
                 FROM C CC
                 WHERE C.EmployeeID = CC.EmployeeID
                    AND C.rownum &gt;= CC.rownum) T(ytd))

SELECT EmployeeID
        ,PlanCode
        ,IssCost
        ,OutOfPocket = CASE WHEN ytd &lt; 400 THEN 0 
                            WHEN (ytd &gt; 400) AND (ytd &lt; (400 + IssCost)) THEN ytd - 400
                            ELSE IssCost
                            END
FROM C2
I think this will work you have to use an inline select statement to get the running total, essentially this is what SUM() OVER function does in 2012+ except the engine was designed to handle the function faster.  

WITH C AS (
    SELECT EmployeeID
            ,PlanCode
            ,IssCost
            ,(SELECT SUM(IssCost) FROM Payroll as b WHERE a.EmployeeID = b.EmployeeID AND a.PlanCode &gt;= b.plancode) as YTD
    FROM Payroll as a   
)
SELECT  EmployeeID
    ,PlanCode
    ,IssCost
    ,YTD
    ,(YTD - 400 + abs(IssCost - 400))/2 AS OutOfPocket
FROM C
ORDER BY EmployeeID, PlanCode