# 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 >= CC.rownum) T(ytd))

SELECT EmployeeID
,PlanCode
,IssCost
,OutOfPocket = CASE WHEN ytd < 400 THEN 0
WHEN (ytd > 400) AND (ytd < (400 + IssCost)) THEN ytd - 400
ELSE IssCost
END
FROM C2

• What sql-server version are you using? – mxix Oct 11 '16 at 17:37
• @mxix Sorry, I am using 2008 R2 – Jeff.Clark Oct 11 '16 at 21:32

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 >= 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


If you are running sqlsever 2012+. You can do it easly with window functions.

select
*
into #Payroll
from (
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)
) x (EmployeeID, PlanCode, IssCost)

select
*,
CASE
WHEN ytd < 400
THEN 0
WHEN (ytd > 400) AND (ytd < (400 + IssCost))
THEN ytd - 400
ELSE IssCost
END OutOfPocket
from (
select
*,
SUM(IssCost) OVER (
PARTITION BY EmployeeID
ORDER BY PlanCode
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) ytd
from #Payroll
) t

drop table #Payroll

• Ack, I forgot to mention that I am on 2008R2. I mentioned in the linked question but neglected to transfer it here. – Jeff.Clark Oct 11 '16 at 20:55